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

关于SQL语句优化的问题

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

请教各位大佬，我有两条查询语句想糅合到一起，试了好久都不行。看哪位大佬有兴趣给瞅瞅。
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=1XF_BUSINESSTYPE in（4，5）

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=0XF_BUSINESSTYPE =4

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

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

试试看是不是你要的结果
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
试试看是不是你要的结果
SELECT
XF_BUSINESSTYPE,

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

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=1XF_BUSINESSTYPE=5（当 ...

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
SELECT
XF_CASHMODE,
XF_BUSINESSTYPE,


谢谢大佬，茅塞顿开:Dweeqw
页: [1]
查看完整版本: 关于SQL语句优化的问题


免责声明：
吾爱破解所发布的一切破解补丁、注册机和注册信息及软件的解密分析文章仅限用于学习和研究目的；不得将上述内容用于商业或者非法用途，否则，一切后果请用户自负。本站信息来自网络，版权争议与本站无关。您必须在下载后的24个小时之内，从您的电脑中彻底删除上述内容。如果您喜欢该程序，请支持正版软件，购买注册，得到更好的正版服务。如有侵权请邮件与我们联系处理。

Mail To:Service@52pojie.cn