关于SQL语句优化的问题

huaxinlinux · 发表于 2023-10-18 17:46

本帖最后由 huaxinlinux 于 2023-10-18 17:49 编辑

请教各位大佬，我有两条查询语句想糅合到一起，试了好久都不行。看哪位大佬有兴趣给瞅瞅。

[SQL] 纯文本查看 复制代码

SELECT
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) THEN XF_AMTSOLD
              ELSE 0 
          END) / 10000, 10) AS today_total_sales,
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) THEN (XF_AMTSOLD-XF_MALLBEARCOUPONAMOUNT-XF_STOREBEARCOUPONAMOUNT)
              ELSE 0 
          END) / 10000, 10) AS today_net_sales,
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TRUNC(SYSDATE, 'MM') AND TRUNC(SYSDATE) THEN XF_AMTSOLD
              ELSE 0 
          END) / 10000, 10) AS month_total_sales,
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TRUNC(SYSDATE, 'MM') AND TRUNC(SYSDATE) THEN (XF_AMTSOLD-XF_MALLBEARCOUPONAMOUNT-XF_STOREBEARCOUPONAMOUNT)
              ELSE 0 
          END) / 10000, 10) AS month_net_sales,
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TO_DATE('2023-05-01', 'YYYY-MM-DD') AND TRUNC(SYSDATE) THEN XF_AMTSOLD
              ELSE 0 
          END) / 10000, 10) AS year_total_sales,
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TO_DATE('2023-05-01', 'YYYY-MM-DD') AND TRUNC(SYSDATE) THEN (XF_AMTSOLD-XF_MALLBEARCOUPONAMOUNT-XF_STOREBEARCOUPONAMOUNT)
              ELSE 0 
          END)/ 10000, 10) AS year_net_sales
FROM
  xf_transsalesitem t, xf_mdcontracth m
WHERE
  t.xf_storecode = m.xf_contractno 
  AND XF_CASHMODE=1
  GROUP BY XF_BUSINESSTYPE;

这一段的XF_CASHMODE=1 XF_BUSINESSTYPE in（4，5）

[SQL] 纯文本查看 复制代码

select * from 
(select round(sum(XF_AMTSOLD)/10000,10) a,
round((sum(XF_AMTSOLD)
-sum(XF_MALLBEARCOUPONAMOUNT)
-sum(XF_STOREBEARCOUPONAMOUNT))/10000,10) b
from xf_transsalesitem t , xf_mdcontracth m
where XF_TXDATE >=trunc(sysdate-1)
and XF_TXDATE<=trunc(sysdate-1)
and t.xf_storecode=m.xf_contractno
and XF_BUSINESSTYPE=4
and XF_CASHMODE=0),
(select round(sum(XF_AMTSOLD)/10000,10) c,
round((sum(XF_AMTSOLD)
-sum(XF_MALLBEARCOUPONAMOUNT)
-sum(XF_STOREBEARCOUPONAMOUNT))/10000,10) d
from xf_transsalesitem t , xf_mdcontracth m
where XF_TXDATE >=trunc(sysdate,'month')
and XF_TXDATE<=trunc(sysdate-1)
and t.xf_storecode=m.xf_contractno
and XF_BUSINESSTYPE=4
and XF_CASHMODE=0),
(select round(sum(XF_AMTSOLD)/10000,10) e,
round((sum(XF_AMTSOLD)
-sum(XF_MALLBEARCOUPONAMOUNT)
-sum(XF_STOREBEARCOUPONAMOUNT))/10000,10) f
from xf_transsalesitem t , xf_mdcontracth m
where XF_TXDATE >=to_date('20230501','yyyymmdd')
and XF_TXDATE<=trunc(sysdate-1)
and t.xf_storecode=m.xf_contractno
and XF_BUSINESSTYPE=4
and XF_CASHMODE=0)

这一段的XF_CASHMODE=0 XF_BUSINESSTYPE =4

是求不同时间段的sum，想通过一条语句查出来，或者有什么更好的写法。

开创者 · 发表于 2023-10-18 18:00

试试看是不是你要的结果

[SQL] 纯文本查看 复制代码

SELECT
  XF_BUSINESSTYPE,
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) AND XF_CASHMODE=1 AND XF_BUSINESSTYPE IN (4, 5) THEN XF_AMTSOLD
              ELSE 0 
          END) / 10000, 10) AS today_total_sales,
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) AND XF_CASHMODE=1 AND XF_BUSINESSTYPE IN (4, 5) THEN (XF_AMTSOLD-XF_MALLBEARCOUPONAMOUNT-XF_STOREBEARCOUPONAMOUNT)
              ELSE 0 
          END) / 10000, 10) AS today_net_sales,
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TRUNC(SYSDATE, 'MM') AND TRUNC(SYSDATE) AND XF_CASHMODE=1 AND XF_BUSINESSTYPE IN (4, 5) THEN XF_AMTSOLD
              ELSE 0 
          END) / 10000, 10) AS month_total_sales,
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TRUNC(SYSDATE, 'MM') AND TRUNC(SYSDATE) AND XF_CASHMODE=1 AND XF_BUSINESSTYPE IN (4, 5) THEN (XF_AMTSOLD-XF_MALLBEARCOUPONAMOUNT-XF_STOREBEARCOUPONAMOUNT)
              ELSE 0 
          END) / 10000, 10) AS month_net_sales,
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TO_DATE('2023-05-01', 'YYYY-MM-DD') AND TRUNC(SYSDATE) AND XF_CASHMODE=1 AND XF_BUSINESSTYPE IN (4, 5) THEN XF_AMTSOLD
              ELSE 0 
          END) / 10000, 10) AS year_total_sales,
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TO_DATE('2023-05-01', 'YYYY-MM-DD') AND TRUNC(SYSDATE) AND XF_CASHMODE=1 AND XF_BUSINESSTYPE IN (4, 5) THEN (XF_AMTSOLD-XF_MALLBEARCOUPONAMOUNT-XF_STOREBEARCOUPONAMOUNT)
              ELSE 0 
          END)/ 10000, 10) AS year_net_sales
FROM
  xf_transsalesitem t, xf_mdcontracth m
WHERE
  t.xf_storecode = m.xf_contractno 
GROUP BY XF_BUSINESSTYPE;

huaxinlinux · 发表于 2023-10-18 19:14

开创者发表于 2023-10-18 18:00
试试看是不是你要的结果
[mw_shl_code=sql,true]SELECT
XF_BUSINESSTYPE,

谢谢大佬，怪我没有表达清楚
满足下列条件得到的三组结果。
1、XF_CASHMODE=1  XF_BUSINESSTYPE=5（当天，当月，当年）
2、XF_CASHMODE=1  XF_BUSINESSTYPE=4（当天，当月，当年）
3、XF_CASHMODE=0  XF_BUSINESSTYPE =4（前一天，当月截至前一天，当年截至前一天）
我试过group by XF_CASHMODE  XF_BUSINESSTYPE
但是查出来的不对。

mmcc1984 · 发表于 2023-10-18 19:57

其实LZ可以试试chatgpt 4.0 我也有一个一直以来困扰我的SQL语句优化问题几年来在不少论坛求助过但限于自己水平无法准确表达得到的回复不能完美解决我的需求后来有了AI 试了各家的之后最终chatgpt 4.0为我解决了问题而且在使用之前我就十分肯定AI可以解决这个问题因为程序语言类是它的强项比人文社科什么的都强绝对的强最主要的是你可以针对一个问题不断的提问不停的完善你的要求它会把你的意思连贯起来直到给出你满意的答案

huaxinlinux · 发表于 2023-10-18 20:35

mmcc1984 发表于 2023-10-18 19:57
其实LZ可以试试chatgpt 4.0 我也有一个一直以来困扰我的SQL语句优化问题几年来在不少论坛求助过但限于 ...

好的，谢谢大佬，我找找看

开创者 · 发表于 2023-10-19 09:15

huaxinlinux 发表于 2023-10-18 19:14
谢谢大佬，怪我没有表达清楚
满足下列条件得到的三组结果。
1、XF_CASHMODE=1 XF_BUSINESSTYPE=5（当 ...

[SQL] 纯文本查看 复制代码

SELECT
  XF_CASHMODE,
  XF_BUSINESSTYPE,
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) AND XF_CASHMODE=1 AND XF_BUSINESSTYPE=5 THEN XF_AMTSOLD
              ELSE 0 
          END) / 10000, 10) AS today_cash_5,
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) AND XF_CASHMODE=1 AND XF_BUSINESSTYPE=4 THEN XF_AMTSOLD
              ELSE 0 
          END) / 10000, 10) AS today_cash_4,
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TRUNC(SYSDATE, 'MM') AND TRUNC(SYSDATE) AND XF_CASHMODE=1 AND XF_BUSINESSTYPE=5 THEN XF_AMTSOLD
              ELSE 0 
          END) / 10000, 10) AS month_cash_5,
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TRUNC(SYSDATE, 'MM') AND TRUNC(SYSDATE) AND XF_CASHMODE=1 AND XF_BUSINESSTYPE=4 THEN XF_AMTSOLD
              ELSE 0 
          END) / 10000, 10) AS month_cash_4,
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TO_DATE('2023-05-01', 'YYYY-MM-DD') AND TRUNC(SYSDATE) AND XF_CASHMODE=1 AND XF_BUSINESSTYPE=5 THEN XF_AMTSOLD
              ELSE 0 
          END) / 10000, 10) AS year_cash_5,
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TO_DATE('2023-05-01', 'YYYY-MM-DD') AND TRUNC(SYSDATE) AND XF_CASHMODE=1 AND XF_BUSINESSTYPE=4 THEN XF_AMTSOLD
              ELSE 0 
          END) / 10000, 10) AS year_cash_4,
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TRUNC(SYSDATE-1) AND TRUNC(SYSDATE-1) AND XF_CASHMODE=0 AND XF_BUSINESSTYPE=4 THEN XF_AMTSOLD
              ELSE 0 
          END) / 10000, 10) AS yesterday_cash_4,
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TRUNC(SYSDATE, 'MM') AND TRUNC(SYSDATE-1) AND XF_CASHMODE=0 AND XF_BUSINESSTYPE=4 THEN XF_AMTSOLD
              ELSE 0 
          END) / 10000, 10) AS month_yesterday_cash_4,
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TO_DATE('2023-05-01', 'YYYY-MM-DD') AND TRUNC(SYSDATE-1) AND XF_CASHMODE=0 AND XF_BUSINESSTYPE=4 THEN XF_AMTSOLD
              ELSE 0 
          END) / 10000, 10) AS year_yesterday_cash_4
FROM
  xf_transsalesitem t, xf_mdcontracth m
WHERE
  t.xf_storecode = m.xf_contractno 
GROUP BY XF_CASHMODE, XF_BUSINESSTYPE;

huaxinlinux · 发表于 2023-10-20 10:02

开创者发表于 2023-10-19 09:15
[mw_shl_code=sql,true]SELECT
XF_CASHMODE,
XF_BUSINESSTYPE,

谢谢大佬，茅塞顿开

帐号		自动登录	找回密码
密码			注册[Register]

[求助] 关于SQL语句优化的问题