SQL 优化问题

Question

优化sql

Answer 1

因为看不到XXX部分的内容，姑且假设只是f和a两表连接以及若干where条件，并且假定这个查询的输出只有4个求sum的字段，即不存在分组。输出的4个字段，前两个的CASE语句条件是相同的，后两个的CASE语句条件也是相同的。那么可以尝试这样改写：select
       shl.cgbyshlid
,      shl.cgbyshl
,      hsje.cgbyjeid
,      hsje.cgbyje
from (
         select
                sum(f.id)
         ,      sum(a.shl * f.rate)
         from XXX
         where f.type = 0
         and   ( datediff(day,f.start_date,a.rq) >= 0 and datediff(day,a.rq,f.end_date) >= 0 )
         and   (
                  (f.creditend > f.credit and a.shl > f.credit and a.shl <= f.creditend) 
               or (f.creditend = f.credit and f.credit <> 0 and a.shl > f.credit) 
               or (f.creditend = f.credit and f.credit = 0)
               )
     ) shl
JOIN (
         select
                sum(f.id)
         ,      sum(a.hsje * f.rate)
         from XXX
         where f.type = 1
         and   ( datediff(day,f.start_date,a.rq) >= 0 and datediff(day,a.rq,f.end_date) >= 0 )
         and   (
                  (f.creditend > f.credit and a.hsje > f.credit and a.hsje <= f.creditend) 
               or (f.creditend = f.credit and f.credit <> 0 and a.hsje > f.credit) 
               or (f.creditend = f.credit and f.credit = 0)
               )
     ) hsje更进一步，这个日期范围的条件“datediff(day,f.start_date,a.rq) >= 0 and datediff(day,a.rq,f.end_date) >= 0”，含义是a.rq的日期部分应该介于f.start_date的日期部分和f.end_date的日期部分之间。我猜这么写是因为这三个字段的类型都是datetime，而程序只想比较日期的范围。那么，其实可以考虑写成“a.rq >= convert(date, f.start_date) and a.rq < dateadd(dd, 1, convert(date, f.end_date))”，因为将datetime字段转化为date，会自动将其时间部分清零，这样可以利用a.rq字段上的索引。并且，这个时间条件对于4个输出字段都是相同的，可以把它单独抽出来放到XXX的部分。
上述方法，把以前的一个查询拆成了两个子查询，但是避免了大量的条件判断。执行效率是否有提升，还是需要观察执行时间和开销的变化，以及分析执行计划。有时候，难看的语句执行效率反而比好看的语句要高~

Answer 2

上面有个错误，两个子查询的连接得写一个伪条件，因为是两个单行求笛卡儿积。更正一下：select
       shl.cgbyshlid
,      shl.cgbyshl
,      hsje.cgbyjeid
,      hsje.cgbyje
from (
         select
                sum(f.id)
         ,      sum(a.shl * f.rate)
         from XXX
         where f.type = 0
         and   ( datediff(day,f.start_date,a.rq) >= 0 and datediff(day,a.rq,f.end_date) >= 0 )
         and   (
                  (f.creditend > f.credit and a.shl > f.credit and a.shl <= f.creditend) 
               or (f.creditend = f.credit and f.credit <> 0 and a.shl > f.credit) 
               or (f.creditend = f.credit and f.credit = 0)
               )
     ) shl
JOIN (
         select
                sum(f.id)
         ,      sum(a.hsje * f.rate)
         from XXX
         where f.type = 1
         and   ( datediff(day,f.start_date,a.rq) >= 0 and datediff(day,a.rq,f.end_date) >= 0 )
         and   (
                  (f.creditend > f.credit and a.hsje > f.credit and a.hsje <= f.creditend) 
               or (f.creditend = f.credit and f.credit <> 0 and a.hsje > f.credit) 
               or (f.creditend = f.credit and f.credit = 0)
               )
     ) hsje ON 1 = 1另外，如果XXX部分里包含where条件，就可以和外部的进行合并。