SQL语句练习实例之十——SQL SERVER 行转列的性能测试

shenzhang

在数据库设计时，有时候为了实现数据规范化的目的，会将属于同一个人的属性记录值改用多条记录的方式来存储，显示时又希望将多个属性数据合并成一行来显示，这就是行转列。
例如：下图的成绩记录表。

那么行转列的效率又将如何呢？我试了以下三种方式进行行转列的测试。测试数据表中的记录数量为120万条，字段为10个，测试下来感觉性能还不错。
一、第一种方式(SQL 2000以后的版本)




--总计120万记录
SELECT wbook_no      
, MAX(CASE WHEN [COP_G_NO] ='60174257' THEN AR END) "60174257"
, MAX(CASE WHEN [COP_G_NO] ='50165814' THEN AR END) "50165814"
, MAX(CASE WHEN [COP_G_NO] ='10221553' THEN AR END) "10221553"
FROM
(
SELECT [COP_G_NO]
, wbook_no
, SUM(G_QTY * decl_Price) AR
FROM WBK_PDE_LIST
WHERE [COP_G_NO] in('60174257','50165814','10221553')
GROUP BY [COP_G_NO]
, wbook_no   
) A
GROUP BY wbook_no


　　
　　二、第二种方式(SQL 2000以后的版本)



SELECT wbook_no
, SUM(
CASE WHEN [COP_G_NO] ='60174257' THEN G_QTY * decl_Price END) "60174257"
, SUM(
CASE WHEN [COP_G_NO] ='50165814' THEN  G_QTY * decl_Price  END) "50165814"
, SUM(
CASE WHEN [COP_G_NO] ='10221553' THEN  G_QTY * decl_Price  END) "10221553"
FROM WBK_PDE_LIST
WHERE 1=1
and [COP_G_NO] in('60174257','50165814','10221553')
GROUP BY wbook_no

　　
　　三、第三种方式：使用PIVOT命令来实现（SQL 2005以后的版本才提供以命令）



SELECT WBOOK_NO
, "60174257"
, "50165814"
, "10221553"
FROM
(
SELECT [COP_G_NO]
, WBOOK_NO
, G_QTY * decl_Price AR
FROM WBK_PDE_LIST
WHERE 1=1
AND [COP_G_NO] in('60174257','50165814','10221553')
)AS D
PIVOT
(
SUM(AR)
FOR [COP_G_NO] in([60174257],[50165814],"10221553")
) AS P

　　
　　

四、以上三种方式的查询结果都如下：


五、最后我们来比对一下各自性能损耗。从比对结果表来看三者的差别不大，总的来说都在2-3秒之间。

	IO			CPU			逻辑读取	物理读取	预读	CPU 时间	占用时间
	表扫描	计算标题	排序	表扫描	计算标题	排序	次	次	次	ms	ms
第一种方式	17.652	0	0.0112513	1.33851	0.121668	0.0131525	23827	370	23827	635	2216
第二种方式	17.652	0	0.0112513	1.33851	0.121668	0.0131525	23827	374	23827	618	2171
第三种方式	17.652	0	0.0112513	1.33851	0.121668	0.0131525	23827	370	23827	563	1960