Sql,少一些join吧

一、这是我自己写的第一段脚本，只考虑了结果，性能完全没考虑

-- 六月份
select 
A.empNo,
A.empName,
convert(decimal(6,3),A.conNum/(A.conNum+B.notConNum+0.0)) as connRate,
'2016-06' as yearMonth
from
(
select empNo,empName,count(0) as conNum
from bill with(nolock)
where beginTime >= '2016-06-01'
and beginTime < '2016-7-01'
and duration > 0
group by empNo,empName
) A
left join 
(
 select empNo,empName,count(0) as notConNum
 from bill with(nolock)
 where beginTime >= '2016-06-01'
 and beginTime < '2016-07-01'
 and duration = 0
 group by empNo,empName
) B on A.empNo = B.empNo
order by A.empNo;

-- 七月份
select 
A.empNo,
A.empName,
convert(decimal(6,3),A.conNum/(A.conNum+B.notConNum+0.0)) as connRate,
'2016-07' as yearMonth
from
(
select empNo,empName,count(0) as conNum
from bill with(nolock)
where beginTime >= '2016-07-01'
and beginTime < '2016-08-01'
and duration > 0
group by empNo,empName
) A
left join 
(
 select empNo,empName,count(0) as notConNum
 from bill with(nolock)
 where beginTime >= '2016-07-01'
 and beginTime < '2016-08-01'
 and duration = 0
 group by empNo,empName
) B on A.empNo = B.empNo
order by A.empNo;

二、这是我优化后的sql，没有减少join，但是从验证结果看性能有明显提升，

看来临时表某些场景下可以作为替换子查询以提高查询性能的一种方式：

-- 六月份
select empNo,empName,count(0) as conNum
into #TMP1
from bill with(nolock)
where beginTime >= '2016-06-01'
and beginTime < '2016-07-01'
and connection = 1
group by empNo,empName;

select empNo,empName,count(0) as notConNum
into #TMP2
from bill with(nolock)
where beginTime >= '2016-06-01'
and beginTime < '2016-07-01'
and connection = 0
group by empNo,empName;

select 
A.empNo,
A.empName,
convert(decimal(6,3),A.conNum/(A.conNum+B.notConNum+0.0)) as connRate,
'2016-06' as yearMonth
from #TMP1 A
left join #TMP2 B on A.empNo = B.empNO
order by A.empNo;

drop table #TMP1;
drop table #TMP2;

-- 七月份略

三、这是DBA优化后的脚本，看过脚本后，整个人不好了，DBA之前

分享过类似的优化技巧，居然没有想到用。这段脚本无论是从简洁性

还是减少io读写上都优化了很多。为了让自己长点记性，整理出来。

select yearMonth
,empNo
,empName
,convert(decimal(6,3),1.0 * conNum/(conNum + notConNum)) as connRate
from (
 select convert(char(7),beginTime,120) AS yearMonth
 ,empNo
 ,empName
 ,sum(case when duration > 0 then 1 else 0 end) conNum
 ,sum(case when duration = 0 then 1 else 0 end) notConNum
 from bill with(nolock)
 where beginTime >= '2016-06-01'
 and beginTime < '2016-08-01'
 group by convert(char(7),beginTime,120),empNo,empName
) t