PIVOT初试

有一个sql

select a.id,a.pid,a.sort,a.[type],b.name,b.pic,isnull((select count(1) from Table1 where ssid=a.id and cType=1 group by ShopIdNew),0) as qcount,isnull((select count(1) from Table1 where ssid=a.id and cType=2 group by ShopIdNew),0) as tcount,isnull((select count(1) from Table1 where ssid=a.id and cType=3 group by ShopIdNew),0) as ccount,isnull((select count(1) from Table1 where ssid=a.id and cType=4 group by ShopIdNew),0) as kcount from temp1 as a  inner join temp2 as b on b.ssid=a.id  where b.Pic!=''

 

对结果集中每一个temp1的id对Table1要扫描4次。

希望只扫描一次，分步来看。

聚合Case when来实现：

select SUM(CASE WHEN cType = 1 THEN 1 END) AS qcount,SUM(CASE WHEN cType = 2 THEN 1 END) AS tcount,SUM(CASE WHEN cType = 3 THEN 1 END) AS ccount,SUM(CASE WHEN cType = 4 THEN 1 END) AS kcountfrom Table1 where ssid = 123

 

这个达到了目标。

但是有个更简单地方法，PIVOT可以代替，据说原理一样，不会提高效率。

SELECT [1] AS qcount, [2] AS tcount, [3] AS ccount, [4] AS kcountFROM (SELECT Id, cType FROM Table1 WHERE Shopid = 161764) AS DPIVOT(COUNT(Id) FOR cType in([1], [2], [3], [4])) AS P

为了加到原来的查询到的结果集中，我们使用APPLY。

select a.id,a.pid,a.sort,a.[type],b.name,b.pic,ISNULL(DT.qcount, 0), ISNULL(DT.tcount, 0), ISNULL(DT.ccount, 0), ISNULL(DT.kcount, 0) from temp1 as a  inner join temp2 as b on b.ssid=a.id OUTER APPLY (SELECT [1] AS qcount, [2] AS tcount, [3] AS ccount, [4] AS kcountFROM (SELECT Id, cType FROM Table1 WHERE ssid = a.id) AS DPIVOT(COUNT(Id) FOR cType in([1], [2], [3], [4])) AS P) AS DTwhere b.Pic!=''

 

运行时间减少，结果满意。