在数据库设计时,有时候为了实现数据规范化的目的,会将属于同一个人的属性记录值改用多条记录的方式来存储,显示时又希望将多个属性数据合并成一行来显示,这就是行转列。
例如:下图的成绩记录表。
那么行转列的效率又将如何呢?我试了以下三种方式进行行转列的测试。测试数据表中的记录数量为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 |