sql语句优化问题

Question

        string sql = "select row_number() over(order by PactSmallDate desc)tmpId,PactSmallDate,";        sql += "(select count(distinct to_id) from T_ChatLogGroup where MsgType=1 and MsgDate=b.PactSmallDate) as QueryCount,";        sql += "(select count(distinct Clientid) from T_SalePact where PactSmallDate=b.PactSmallDate and exists(select top 1 'a' from T_ChatLogGroup where MsgType=1 and to_id=T_SalePact.buyer_nick and MsgDate=b.PactSmallDate)) as SuccessCountTotay,";        sql += "(select count(distinct Clientid) from T_SalePact where (PactSmallDate=b.PactSmallDate or PactSmallDate=DATEADD(d,1,b.PactSmallDate)) and exists(select top 1 'a' from T_ChatLogGroup where MsgType=1 and to_id=T_SalePact.buyer_nick and MsgDate=b.PactSmallDate)) as SuccessCountLast,";        sql += "(select count(distinct Clientid) from T_SalePact where pay_time>'1970-01-01 00:00:00.000' and PactSmallDate=b.PactSmallDate and exists(select top 1 'a' from T_ChatLogGroup where MsgType=1 and to_id=T_SalePact.buyer_nick and MsgDate=b.PactSmallDate)) as PayLast,";        sql += "(select count(distinct Clientid) from T_SalePact where PactSmallDate=b.PactSmallDate and pay_time<=(Convert(varchar,b.PactSmallDate)+' 23:59:59') and pay_time>=(Convert(varchar,b.PactSmallDate)+' 00:00:00') and exists(select top 1 'a' from T_ChatLogGroup where MsgType=1 and to_id=T_SalePact.buyer_nick and MsgDate=b.PactSmallDate)) as PayTotay";        sql += " from T_SalePact b group by PactSmallDate";
        sql = "select * from (" + sql + ")tmpTable where tmpId between 1 and 100 order by PactSmallDate desc";想了解的是，用不同条件去同一个表中统计数据，能不能合并在一起写？这样就能避免多次去操作表了
或者，我上面的sql语句是否有优化的地方？望大侠赐教，分不够可再加。

Answer 1

怎么个union法，不要这么简单的回复一下
我每次提问前，都尽我所能找遍了google和baidu，并试过了多种方法之后才来提问的，你这么似是而非的回答，还不如不要回。

Answer 2

我发的问题都没有认真看完，就看了最后问的能否合并，就说用union合并，真是服了你。

Answer 3

合并是可以的
但是
1、要分析数据情况，看能不能将exists改成内连接
2、要综合各个子句的条件，形成一个宽松的条件
3、用case when 实现各子句的合并需要指出的是，因为宽松了条件，这么修改是否真的能起到优化作用并非是一定的

Answer 4

to BigInt：
很感谢您这么专业的指点，我会测试一下的。期待有更多高人来指点。

Answer 5

可以用case when判断啊 
select
   sum(case when  pay_time>'1970-01-01 00:00:00.000' then 1 else 0 end) as num,
   sum(case when  pay_time<=(Convert(varchar,b.PactSmallDate)+' 23:59:59' then 1 else 0 end) as num2
...
from
   a,b
where
   a.PactSmallDate=b.PactSmallDate 
and
   exists(select top 1 'a' from T_ChatLogGroup where MsgType=1 and to_id=T_SalePact.buyer_nick and MsgDate=b.PactSmallDate)

Answer 6

exists(select top 1 'a' from T_ChatLogGroup 
        where MsgType=1 and to_id=T_SalePact.buyer_nick 
           and MsgDate=b.PactSmallDate)
      )这句在每个子查询里都有，可以把它直接放到外表的where条件里筛选。试着写成：exists(select 1 from T_ChatLogGroup 
        where MsgType=1 and to_id=T_SalePact.buyer_nick 
           and MsgDate=b.PactSmallDate)
      )其他count统计可以用 sum(case when .. then .. else .. end) as ..,
这种方式实现。

Answer 7

请大侠把我的语句转成case when,我转的时候会有这样的错误提示：
当没有用 EXISTS 引入子查询时，在选择列表中只能指定一个表达式。

Answer 8

1. 建议不要在程序中拼SQL，用存储过程，这一步可以提高性能
2. 具体SQL语句的效率随着表的结构、索引等不尽相同，需要分析各语句的执行计划以选择最佳

Answer 9

我现在只想知道，如果把我上面的sql语句如下4句：
  sql += "(select count(distinct Clientid) from T_SalePact where PactSmallDate=b.PactSmallDate and exists(select top 1 'a' from T_ChatLogGroup where MsgType=1 and to_id=T_SalePact.buyer_nick and MsgDate=b.PactSmallDate)) as SuccessCountTotay,";  sql += "(select count(distinct Clientid) from T_SalePact where (PactSmallDate=b.PactSmallDate or PactSmallDate=DATEADD(d,1,b.PactSmallDate)) and exists(select top 1 'a' from T_ChatLogGroup where MsgType=1 and to_id=T_SalePact.buyer_nick and MsgDate=b.PactSmallDate)) as SuccessCountLast,";  sql += "(select count(distinct Clientid) from T_SalePact where pay_time>'1970-01-01 00:00:00.000' and PactSmallDate=b.PactSmallDate and exists(select top 1 'a' from T_ChatLogGroup where MsgType=1 and to_id=T_SalePact.buyer_nick and MsgDate=b.PactSmallDate)) as PayLast,";  sql += "(select count(distinct Clientid) from T_SalePact where PactSmallDate=b.PactSmallDate and pay_time<=(Convert(varchar,b.PactSmallDate)+' 23:59:59') and pay_time>=(Convert(varchar,b.PactSmallDate)+' 00:00:00') and exists(select top 1 'a' from T_ChatLogGroup where MsgType=1 and to_id=T_SalePact.buyer_nick and MsgDate=b.PactSmallDate)) as PayTotay";改成用case when，该如何改才能形成一个正确的sql语句？

Answer 10

select row_number() over(order by PactSmallDate desc)tmpId,PactSmallDate,
(select count(distinct to_id) from T_ChatLogGroup where MsgType=1 and MsgDate=b.PactSmallDate) as QueryCount,
(select count(distinct Clientid))  as SuccessCountTotay,
---这段SuccessCountLast因为PactSmallDate=DATEADD(d,1,b.PactSmallDate)，不做优化设置
(select count(distinct Clientid) from T_SalePact where (PactSmallDate=b.PactSmallDate 
or PactSmallDate=DATEADD(d,1,b.PactSmallDate)) 
and exists(select top 1 'a' from T_ChatLogGroup 
where MsgType=1 and to_id=T_SalePact.buyer_nick and MsgDate=b.PactSmallDate)) as SuccessCountLast,
---------------------------------------------------------------------------------------------------
PayLast =count(distinct(case when pay_time>'1970-01-01 00:00:00.000' then Clientid end)),
PayTotay=count(distinct(case when pay_time<=(Convert(varchar,b.PactSmallDate)+' 23:59:59') 
                              and pay_time>=(Convert(varchar,b.PactSmallDate)+' 00:00:00')  
                                                                    then Clientid end))
 from T_SalePact b
where 
      exists(select top 1 'a' from T_ChatLogGroup 
                 where MsgType=1 and to_id=b.buyer_nick and MsgDate=b.PactSmallDate)
group by b.PactSmallDate

Answer 11

你这种语句属于头重的那种 每得到一行结果记录都要count5次 这种语句往往效率极低
我这里有以前别人写的和你这个差不多的语句 速度低得惊人 查出17000多行记录估计得3，4个小时
一般优化的方法是
比如:
"(select count(distinct Clientid) from T_SalePact where PactSmallDate=b.PactSmallDate and exists(select top 1 'a' from T_ChatLogGroup where MsgType=1 and to_id=T_SalePact.buyer_nick and MsgDate=b.PactSmallDate)) as SuccessCountTotay这种 最好是按照PactSmallDate，MsgDate分组单独的group by 出来到临时表或者其它什么里面  然后再select 关联到b表的PactSmallDate
其它的4列也这么处理。

Answer 12

通过这样的处理之后相信会得到结果集的速度会有质的飞跃。
查出17000多行记录的那个查询通过这样类似的处理现在只需要1分钟不到的时间。、
理论上来说效率是以前查询效率的17000多倍 呵呵。
因为每次得到的记录都不用重复的去计算count了