关于一段查询语句的优化问题
自用的一段查询语句，总感觉不够简练，想看有没有感兴趣的大佬能怎么优化一下。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。
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上的一个人呢 ？天天算这东西 为啥不用Python提取，直接复用 非常好非常好非常好非常好 哈哈，盲猜楼主的项目是传统行业国企，项目里经常看到Oracle数据库的超长SQL
当时实在优化不动的时候，只要不涉及排序分页的，尽量就多次查询然后再让Java层去解决 phenoixkaixuan 发表于 2023-10-13 07:21
哈哈，盲猜楼主的项目是传统行业国企，项目里经常看到Oracle数据库的超长SQL
当时实在优化不动的时候，只 ...
猜的真准 开创者 发表于 2023-10-12 19:04
SELECT
ROUND(SUM(CASE
WHEN XF_TXDATE BETWEEN TRUNC(SYSDATE ...
大神威武，拿来就能用，我得好好学习下 开创者 发表于 2023-10-12 19:05
对了，这里的日期范围和格式可能需要根据你的需求进行调整。此查询将返回每天、每月和每年的销售总额和净销 ...
我自学了点皮毛，也是整天瞎琢磨，可能不是你说的那个人。
页:
[1]
2