PIVOT初试-优快云博客

本文链接：https://blog.youkuaiyun.com/pigHead_chen/article/details/8166031

本文详细介绍了如何通过聚合CASE WHEN表达式、使用PIVOT操作和APPLY方法来优化SQL查询，减少对Table1表的多次扫描，从而提高查询性能。通过实例演示了如何将复杂查询简化为更高效的SQL语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

有一个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 kcount
	from Table1 
		where ssid = 123

这个达到了目标。

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

SELECT [1] AS qcount, [2] AS tcount, [3] AS ccount, [4] AS kcount
FROM (SELECT Id, cType FROM Table1 WHERE Shopid = 161764) AS D
	PIVOT(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 kcount
			FROM (SELECT Id, cType FROM Table1 WHERE ssid = a.id) AS D
				PIVOT(COUNT(Id) FOR cType in([1], [2], [3], [4])) AS P) AS DT
	where b.Pic!=''

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