关于一段查询语句的优化问题

huaxinlinux

自用的一段查询语句，总感觉不够简练，想看有没有感兴趣的大佬能怎么优化一下。


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)
and XF_TXDATE<=trunc(sysdate)
and t.xf_storecode=m.xf_contractno
and XF_BUSINESSTYPE=5),
(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)
and t.xf_storecode=m.xf_contractno
and XF_BUSINESSTYPE=5),
(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)
and t.xf_storecode=m.xf_contractno
and XF_BUSINESSTYPE=5)

目的就是按照当天，当月，当年查询两个列的sum。

开创者

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

?

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

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               ELSE 0           END) - SUM(CASE                           WHEN XF_TXDATE BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) THEN XF_MALLBEARCOUPONAMOUNT                           ELSE 0                       END) - SUM(CASE                                     WHEN XF_TXDATE BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) THEN 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               ELSE 0           END) - SUM(CASE                           WHEN XF_TXDATE BETWEEN TRUNC(SYSDATE, 'MM') AND TRUNC(SYSDATE) THEN XF_MALLBEARCOUPONAMOUNT                           ELSE 0                       END) - SUM(CASE                                     WHEN XF_TXDATE BETWEEN TRUNC(SYSDATE, 'MM') AND TRUNC(SYSDATE) THEN XF_STOREBEARCOUPONAMOUNT                                     ELSE 0                                 END)) / 10000, 10) AS month_net_sales,   ROUND(SUM(CASE               WHEN XF_TXDATE BETWEEN TO_DATE('2023-01-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-01-01', 'YYYY-MM-DD') AND TRUNC(SYSDATE) THEN XF_AMTSOLD               ELSE 0           END) - SUM(CASE                           WHEN XF_TXDATE BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') AND TRUNC(SYSDATE) THEN XF_MALLBEARCOUPONAMOUNT                           ELSE 0                       END) - SUM(CASE                                     WHEN XF_TXDATE BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') AND TRUNC(SYSDATE) THEN 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_BUSINESSTYPE = 5;

开创者

对了，这里的日期范围和格式可能需要根据你的需求进行调整。此查询将返回每天、每月和每年的销售总额和净销售总额。
试试看是你需要的不是。
怎么感觉你像我QQ上的一个人呢 ？天天算这东西

sai609

为啥不用Python提取，直接复用

zwxtx

非常好非常好非常好非常好

phenoixkaixuan

哈哈，盲猜楼主的项目是传统行业国企，项目里经常看到Oracle数据库的超长SQL
当时实在优化不动的时候，只要不涉及排序分页的，尽量就多次查询然后再让Java层去解决

huaxinlinux

phenoixkaixuan 发表于 2023-10-13 07:21
哈哈，盲猜楼主的项目是传统行业国企，项目里经常看到Oracle数据库的超长SQL
当时实在优化不动的时候，只 ...

猜的真准

huaxinlinux

开创者 发表于 2023-10-12 19:04
[mw_shl_code=sql,true]SELECT
  ROUND(SUM(CASE
              WHEN XF_TXDATE BETWEEN TRUNC(SYSDATE ...

大神威武，拿来就能用，我得好好学习下

huaxinlinux

开创者 发表于 2023-10-12 19:05
对了，这里的日期范围和格式可能需要根据你的需求进行调整。此查询将返回每天、每月和每年的销售总额和净销 ...

我自学了点皮毛，也是整天瞎琢磨，可能不是你说的那个人。