帮忙优化一个SQL语句

Question

select a.riqi,
       a.yinghuisum,
       a.yinghuiMoney,
       b.shishousum,
       b.shishouMoney,
       c.notReturnsum,
       c.notReturnMoney
  from (select TO_DATE(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD') as riqi,
               count(t.flowid) as yinghuisum /*应回款订单数*/,
               sum(o.ordersum) as yinghuiMoney
          from ordersinfo o, postdealinfo t
         where o.orderid = t.orderid
           and TO_DATE(substr(t.postdealtime, 0, 14),
                       'YYYY-MM-DD HH24:MI:SS') >=
               TO_DATE('2009-01-01 16:46:14', 'YYYY-MM-DD HH24:MI:SS')
           and TO_DATE(substr(t.postdealtime, 0, 14),
                       'YYYY-MM-DD HH24:MI:SS') <=
               TO_DATE('2009-08-29 16:46:14', 'YYYY-MM-DD HH24:MI:SS')
           and not exists
         (select *
                  from orderstatuhisinfo sta
                 where t.orderid = sta.orderid
                   and (sta.lststatu = '11' or sta.lststatu = '19'))
         group by substr(t.postdealtime, 0, 8)) a
  left join (select TO_DATE(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD') as riqi,
                    count(t.flowid) as shishousum /*实回款单数*/,
                    sum(o.ordersum) as shishouMoney
               from ordersinfo o, postdealinfo t
              where o.orderid = t.orderid
                and TO_DATE(substr(t.postdealtime, 0, 14),
                            'YYYY-MM-DD HH24:MI:SS') >=
                    TO_DATE('2009-01-01 16:46:14', 'YYYY-MM-DD HH24:MI:SS')
                and TO_DATE(substr(t.postdealtime, 0, 14),
                            'YYYY-MM-DD HH24:MI:SS') <=
                    TO_DATE('2009-08-29 16:46:14', 'YYYY-MM-DD HH24:MI:SS')
                and exists (select *
                       from orderstatuhisinfo sta
                      where t.orderid = sta.orderid
                        and sta.lststatu = '17')
              group by substr(t.postdealtime, 0, 8)) b on b.riqi = a.riqi
  left join (select TO_DATE(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD') as riqi,
                    count(t.flowid) as notReturnsum /*未回款单数*/,
                    sum(o.ordersum) as notReturnMoney
               from ordersinfo o, postdealinfo t
              where o.orderid = t.orderid
                and TO_DATE(substr(t.postdealtime, 0, 14),
                            'YYYY-MM-DD HH24:MI:SS') >=
                    TO_DATE('2009-01-01 16:46:14', 'YYYY-MM-DD HH24:MI:SS')
                and TO_DATE(substr(t.postdealtime, 0, 14),
                            'YYYY-MM-DD HH24:MI:SS') <=
                    TO_DATE('2009-08-29 16:46:14', 'YYYY-MM-DD HH24:MI:SS')
                and not exists
              (select *
                       from orderstatuhisinfo sta
                      where t.orderid = sta.orderid
                        and (sta.lststatu = '11' or sta.lststatu = '19' or
                            sta.lststatu = '17'))
              group by substr(t.postdealtime, 0, 8)) c on a.riqi = c.riqi

Answer 1

不要对你的字段进行函数操作,把那些to_date的地方，把相应的地方的查询条件改成字段串比较

Answer 2

这样的字符串也是可以比对的，用 >= 和 <=等比较就行了

Answer 3

to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')

Answer 4

你把这个日期再转化成字符串啊，很简单的事情啊to_char(TO_DATE('2009-01-01 16:46:14', 'YYYY-MM-DD HH24:MI:SS'), 'yyyymmddhh24miss')这不就可以比较了嘛

Answer 5

select TO_DATE(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD') riqi,
       count(case when not exists(
                      select * from orderstatuhisinfo sta
                     where t.orderid = sta.orderid
                      and (sta.lststatu = '11' or sta.lststatu = '19'))
                      then t.flowid end)yinghuisum,
       sum(case when not exists(
                      select * from orderstatuhisinfo sta
                     where t.orderid = sta.orderid
                      and (sta.lststatu = '11' or sta.lststatu = '19'))
                      then o.ordersum end)yinghuiMoney,
       count(case when exists (select *
                       from orderstatuhisinfo sta
                      where t.orderid = sta.orderid
                        and sta.lststatu = '17')
                      then t.flowid end)shishousum,
       sum(case when exists (select *
                       from orderstatuhisinfo sta
                      where t.orderid = sta.orderid
                        and sta.lststatu = '17')
                      then o.ordersum end)shishouMoney,
       count(case when not exists(select 1
                       from orderstatuhisinfo sta
                      where t.orderid = sta.orderid
                        and sta.lststatu in ('11','19','17'))
                       then t.flowid end)notReturnsum,
       sum(case when not exists(select 1
                       from orderstatuhisinfo sta
                      where t.orderid = sta.orderid
                        and sta.lststatu in ('11','19','17'))
                      then o.ordersum end)notReturnMoney                
from ordersinfo o, postdealinfo t
   where o.orderid = t.orderid
      and t.postdealtime>=to_char(to_date('2009-01-01 16:46:14','YYYY-MM-DD HH24:MI:SS'),'YYYYMMDDHH24MISS')
      and t.postdealtime <to_char(to_date('2009-08-29 16:46:15','YYYY-MM-DD HH24:MI:SS'),'YYYYMMDDHH24MISS')
      group by substr(t.postdealtime, 0, 8) 先试试这个代码，运行一下比较效率
count和sum里面的条件重复了，还没想到更好的办法

Answer 6

SELECT to_date(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD') AS riqi,
            COUNT(CASE WHEN sta.lststatu NOT IN ('11','19') THEN 1 END) AS yinghuisum /*应回款订单数*/,
            SUM(CASE WHEN sta.lststatu NOT IN ('11','19') THEN o.ordersum END) AS  AS shishousum,
            COUNT(CASE WHEN sta.lststatu = '17' THEN 1 END) AS shishoumoney /*实回款单数*/,
            SUM(CASE WHEN sta.lststatu = '17' THEN o.ordersum END) AS  AS yinghuimoney,
            COUNT(CASE WHEN sta.lststatu NOT IN ('11','19','17') THEN 1 END) AS notreturnsum /*未回款单数*/,
            SUM(CASE WHEN sta.lststatu NOT IN ('11','19','17') THEN o.ordersum END) AS  AS notreturnmoney,
FROM   ordersinfo o, postdealinfo t,orderstatuhisinfo sta
WHERE  o.orderid = t.orderid
  AND  t.orderid = sta.orderid
  and  t.postdealtime>=to_char(to_date('2009-01-01 16:46:14','YYYY-MM-DD HH24:MI:SS'),'YYYYMMDDHH24MISS')
  and  t.postdealtime < to_char(to_date('2009-08-29 16:46:15','YYYY-MM-DD HH24:MI:SS'),'YYYYMMDDHH24MISS')
GROUP BY  to_date(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD') 

Answer 7

原来还有这么长的SQL语句啊，原作者要是离职了，接手人能读明白吗？

Answer 8

TO_DATE(substr(t.postdealtime, 0, 14),'YYYY-MM-DD HH24:MI:SS')可以建立函数索引~

Answer 9

把连接条件改下t.orderid = sta.orderid改成
t.orderid = sta.orderid(+)
再看看
      

Answer 10

SELECT to_date(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD') AS riqi,            
       SUM(CASE WHEN sta.lststatu NOT IN ('11','19') THEN 1 ELSE 0 END) AS yinghuisum /*应回款订单数*/,
       SUM(CASE WHEN sta.lststatu NOT IN ('11','19') THEN o.ordersum ELSE 0 END) AS yinghuiMoney,
       SUM(CASE WHEN sta.lststatu = '17' THEN 1 ELSE 0 END) AS shishousum /*实回款单数*/,
       SUM(CASE WHEN sta.lststatu = '17' THEN o.ordersum ELSE 0 END) AS shishouMoney,
       SUM(CASE WHEN sta.lststatu NOT IN ('11','19','17') THEN 1 ELSE 0 END) AS notReturnsum /*未回款单数*/,
       SUM(CASE WHEN sta.lststatu NOT IN ('11','19','17') THEN o.ordersum ELSE 0 END) AS notReturnMoney,
FROM   ordersinfo o, postdealinfo t,orderstatuhisinfo sta
WHERE  o.orderid        = t.orderid
AND    t.orderid        = sta.orderid
and    t.postdealtime   >= to_char(to_date('2009-01-01 16:46:14','YYYY-MM-DD HH24:MI:SS'),'YYYYMMDDHH24MISS')
and    t.postdealtime   <= to_char(to_date('2009-08-29 16:46:14','YYYY-MM-DD HH24:MI:SS'),'YYYYMMDDHH24MISS')
GROUP BY  to_date(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD');改了下9楼的，试试吧

Answer 11

噢发现个问题，统计的应该是flowid,再次修改：
SELECT to_date(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD') AS riqi,            
       SUM(CASE WHEN sta.lststatu NOT IN ('11','19') AND t.flowid IS NOT NULL THEN 1 ELSE 0 END) AS yinghuisum /*应回款订单数*/,
       SUM(CASE WHEN sta.lststatu NOT IN ('11','19') THEN o.ordersum ELSE 0 END) AS yinghuiMoney,
       SUM(CASE WHEN sta.lststatu = '17' AND t.flowid IS NOT NULL THEN 1 ELSE 0 END) AS shishousum /*实回款单数*/,
       SUM(CASE WHEN sta.lststatu = '17' THEN o.ordersum ELSE 0 END) AS shishouMoney,
       SUM(CASE WHEN sta.lststatu NOT IN ('11','19','17') AND t.flowid IS NOT NULL THEN 1 ELSE 0 END) AS notReturnsum /*未回款单数*/,
       SUM(CASE WHEN sta.lststatu NOT IN ('11','19','17') THEN o.ordersum ELSE 0 END) AS notReturnMoney
FROM   ordersinfo o, postdealinfo t,orderstatuhisinfo sta
WHERE  o.orderid        = t.orderid
AND    t.orderid        = sta.orderid
and    t.postdealtime   >= to_char(to_date('2009-01-01 16:46:14','YYYY-MM-DD HH24:MI:SS'),'YYYYMMDDHH24MISS')
and    t.postdealtime   <= to_char(to_date('2009-08-29 16:46:14','YYYY-MM-DD HH24:MI:SS'),'YYYYMMDDHH24MISS')
GROUP BY  to_date(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD');

Answer 12

select TO_DATE(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD') as riqi,
               count(t.flowid) as yinghuisum /*应回款订单数*/,
               sum(o.ordersum) as yinghuiMoney
          from ordersinfo o, postdealinfo t
         where o.orderid = t.orderid
           and TO_DATE(substr(t.postdealtime, 0, 14),
                       'YYYY-MM-DD HH24:MI:SS') >=
               TO_DATE('2009-01-01 16:46:14', 'YYYY-MM-DD HH24:MI:SS')
           and TO_DATE(substr(t.postdealtime, 0, 14),
                       'YYYY-MM-DD HH24:MI:SS') <=
               TO_DATE('2009-08-29 16:46:14', 'YYYY-MM-DD HH24:MI:SS')
           and not exists
         (select *
                  from orderstatuhisinfo sta
                 where t.orderid = sta.orderid
                   and (sta.lststatu = '11' or sta.lststatu = '19'))
         group by substr(t.postdealtime, 0, 8);
         
         
SELECT to_date(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD') AS riqi,            
       SUM(CASE WHEN sta.lststatu NOT IN ('11','19') AND t.flowid IS NOT NULL THEN 1 ELSE 0 END) AS yinghuisum /*应回款订单数*/,
       SUM(CASE WHEN sta.lststatu NOT IN ('11','19') THEN o.ordersum ELSE 0 END) AS yinghuiMoney
       --SUM(CASE WHEN sta.lststatu = '17' AND t.flowid IS NOT NULL THEN 1 ELSE 0 END) AS shishousum /*实回款单数*/,
       --SUM(CASE WHEN sta.lststatu = '17' THEN o.ordersum ELSE 0 END) AS shishouMoney,
       --SUM(CASE WHEN sta.lststatu NOT IN ('11','19','17') AND t.flowid IS NOT NULL THEN 1 ELSE 0 END) AS notReturnsum /*未回款单数*/,
       --SUM(CASE WHEN sta.lststatu NOT IN ('11','19','17') THEN o.ordersum ELSE 0 END) AS notReturnMoney
FROM   ordersinfo o, postdealinfo t,orderstatuhisinfo sta
WHERE  o.orderid        = t.orderid
AND    t.orderid        = sta.orderid
and    t.postdealtime   >= to_char(to_date('2009-01-01 16:46:14','YYYY-MM-DD HH24:MI:SS'),'YYYYMMDDHH24MISS')
and    t.postdealtime   <= to_char(to_date('2009-08-29 16:46:14','YYYY-MM-DD HH24:MI:SS'),'YYYYMMDDHH24MISS')
GROUP BY  to_date(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD');把你的sql拆开分成3部分，和这部分执行，然后单条查看是哪些数据不一致。列出3个表的主键和表结构

Answer 13

啊明白了，为什么只有实收是对的，是因为不仅有sta.lststatu NOT IN ('11','19') 还有可能sta.lststatu是空的，需要使用左连接，9楼结果不正确是因为没有处理sta.lststatu is null的情况刚才测试了下，如果某列值为null，当我们使用case when .. not in ('11','13')却并没有统计这列空值。代码再修改下，试试吧
SELECT to_date(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD') AS riqi,            
       SUM(CASE WHEN sta.lststatu IN ('11','19') THEN 0 ELSE 1 END) AS yinghuisum /*应回款订单数*/,
       SUM(CASE WHEN sta.lststatu IN ('11','19') THEN 0 ELSE o.ordersum END) AS yinghuiMoney,
       SUM(CASE WHEN sta.lststatu = '17' THEN 1 ELSE 0 END) AS shishousum /*实回款单数*/,
       SUM(CASE WHEN sta.lststatu = '17' THEN o.ordersum ELSE 0 END) AS shishouMoney,
       SUM(CASE WHEN sta.lststatu IN ('11','19','17') THEN 0 ELSE 1 END) AS notReturnsum /*未回款单数*/,
       SUM(CASE WHEN sta.lststatu IN ('11','19','17') THEN 0 ELSE o.ordersum END) AS notReturnMoney
FROM   ordersinfo o, postdealinfo t,orderstatuhisinfo sta
WHERE  o.orderid        = t.orderid
AND    t.orderid        = sta.orderid(+)
and    t.postdealtime   >= to_char(to_date('2009-01-01 16:46:14','YYYY-MM-DD HH24:MI:SS'),'YYYYMMDDHH24MISS')
and    t.postdealtime   <= to_char(to_date('2009-08-29 16:46:14','YYYY-MM-DD HH24:MI:SS'),'YYYYMMDDHH24MISS')
GROUP BY  to_date(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD');

Answer 14

为什么这两个SQL执行出来的结果不一样呢，条件可都是相同的啊！select TO_DATE(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD') as riqi,
count(t.flowid) as yinghuisum /*应回款订单数*/ 
from orderstatuhisinfo sta,ordersinfo o,postdealinfo t
 where o.orderid=sta.orderid and t.orderid=o.orderid and
 sta.lststatu not in('11','19')
 and TO_DATE(substr(t.postdealtime, 0, 14),
                       'YYYY-MM-DD HH24:MI:SS') >=
               TO_DATE('2009-01-01 16:46:14', 'YYYY-MM-DD HH24:MI:SS')
           and TO_DATE(substr(t.postdealtime, 0, 14),
                       'YYYY-MM-DD HH24:MI:SS') <=
               TO_DATE('2009-08-29 16:46:14', 'YYYY-MM-DD HH24:MI:SS')
group by substr(t.postdealtime, 0, 8)select TO_DATE(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD') as riqi,
               count(t.flowid) as yinghuisum /*应回款订单数*/,
               sum(o.ordersum) as yinghuiMoney
          from ordersinfo o, postdealinfo t
         where o.orderid = t.orderid
           and TO_DATE(substr(t.postdealtime, 0, 14),
                       'YYYY-MM-DD HH24:MI:SS') >=
               TO_DATE('2009-01-01 16:46:14', 'YYYY-MM-DD HH24:MI:SS')
           and TO_DATE(substr(t.postdealtime, 0, 14),
                       'YYYY-MM-DD HH24:MI:SS') <=
               TO_DATE('2009-08-29 16:46:14', 'YYYY-MM-DD HH24:MI:SS')
           and not exists
         (select *
                  from orderstatuhisinfo sta
                 where t.orderid = sta.orderid
                   and (sta.lststatu = '11' or sta.lststatu = '19'))
         group by substr(t.postdealtime, 0, 8)

Answer 15

23楼现在应该是对的了是应该不一样的因为sta.lststatu in('11','19')的对应的反面应该是：sta.lststatu not in('11','19') or sta.lststatu is null;而后面我们的修改都未考虑sta.lststatu is null的情况

Answer 16

sta.lststatu不可能是空的，orderstatuhisinfo表是订单状态记录表

Answer 17

not exists (select *from orderstatuhisinfo sta
where t.orderid = sta.orderid
and(sta.lststatu = '11' or sta.lststatu = '19'))对于这句来说，满足其要求的可能有:
1.t的orderid在sta表中出现且sta.lststatu != 11 或者sta.lststatu != 19
2.t的orderid没有在sta表出现当修改用上左连接后，第2种情况下查询出来的sta.lststatu就是null另：
23楼的结果是对的吗？
CASE WHEN sta.lststatu IN ('11','19') THEN 0 ELSE 1 END
等价于：
CASE WHEN sta.lststatu NOT IN ('11','19') OR sta.lststatu IS NULL THEN 1 ELSE 0 END

Answer 18

有一个简单的方法可以判定是否t.orderid的数量和sta.orderid一致select count(distinct orderid) from t where time between ... and .;
select count(distinct orderid) from sta;
看结果是否相同“sta.lststatu != 11 或者sta.lststatu != 19”应该是：
“sta.lststatu != 11 且sta.lststatu != 19”

Answer 19

SELECT to_date(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD')riqi,            
            COUNT(t.flowid) yinghuisum /*应回款订单数*/,
            SUM(o.ordersum) shishousum,
            COUNT(CASE WHEN sta.lststatu = '17' THEN t.flowid END) shishoumoney /*实回款单数*/,
            SUM(CASE WHEN sta.lststatu = '17' THEN o.ordersum END) yinghuimoney,
            COUNT(CASE WHEN sta.lststatu<>'17' THEN t.flowid END) notreturnsum /*未回款单数*/,
            SUM(CASE WHEN sta.lststatu<>'17' THEN o.ordersum END) notreturnmoney,
FROM   ordersinfo o inner join postdealinfo t
on  o.orderid = t.orderid
left join orderstatuhisinfo sta
on t.orderid = sta.orderid
 where   t.postdealtime>=to_char(to_date('2009-01-01 16:46:14','YYYY-MM-DD HH24:MI:SS'),'YYYYMMDDHH24MISS')
  and     t.postdealtime < to_char(to_date('2009-08-29 16:46:15','YYYY-MM-DD HH24:MI:SS'),'YYYYMMDDHH24MISS')
 and (sta.lststatu NOT IN ('11','19') or sta.lststatu is null)
GROUP BY  to_date(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD') 这个怎么样

Answer 20

shiyiwan也发现了，嘿嘿
我有事，闪人