数据库环境：sql SERVER 2005
现有一个产品销售实时表，表数据如下：
字段name是产品名称，字段type是销售类型，1表示售出，2表示退货，字段num是数量，字段ctime是操作时间。
要求：
在一行中统计24小时内所有货物的销售（售出，退货）数据，把日期考虑在内。
分析：
这实际上是行转列的一个应用，在进行行转列之前，需要补全24小时的所有数据。补全数据可以通过系统的数字辅助表
spt_values来实现，进行行转列时，根据type和处理后的ctime分组即可。
1.建表，导入数据
sql;">
CREATE TABLE snake (name VARCHAR(10 ),type INT,num INT,ctime DATETIME )
INSERT INTO snake VALUES(' 方便面',1,10,'2015-08-10 16:20:05')
INSERT INTO snake VALUES(' 香烟A ',2,'2015-08-10 18:21:10')
INSERT INTO snake VALUES(' 香烟A ',5,'2015-08-10 20:21:10')
INSERT INTO snake VALUES(' 香烟B',6,9,'2015-08-10 20:21:10')
INSERT INTO snake VALUES(' 香烟C','2015-08-10 20:21:10')
2.补全24小时的数据
= 0
AND number <= 23
),/*找出表所有的日期*/
x1
AS ( SELECT DISTINCT
CONVERT(VARCHAR(100),ctime,23) AS d
FROM snake
),/*补全所有日期的24小时*/
x2
AS ( SELECT x1.d,x0.h
FROM x1
CROSS JOIN x0
),x3
AS ( SELECT name,type,num,DATEPART(hour,ctime) AS h
FROM snake
),/*整理行转列需要用到的数据*/
x4
AS ( SELECT x2.d,x2.h,x3.name,x3.type,x3.num
FROM x2
LEFT JOIN x3 ON x3.h = x2.h
)
3.行转列
sql;">
SELECT ISNULL([0],0) AS [00],ISNULL([1],0) AS [01],ISNULL([2],0) AS [02],ISNULL([3],0) AS [03],ISNULL([4],0) AS [04],ISNULL([5],0) AS [05],ISNULL([6],0) AS [06],7) AS [07],ISNULL([8],0) AS [08],ISNULL([9],0) AS [09],ISNULL([10],0) AS [10],11) AS [11],ISNULL([12],0) AS [12],ISNULL([13],0) AS [13],ISNULL([14],0) AS [14],15) AS [15],ISNULL([16],0) AS [16],ISNULL([17],0) AS [17],ISNULL([18],0) AS [18],ISNULL([19],15) AS [19],ISNULL([20],0) AS [20],ISNULL([21],0) AS [21],ISNULL([22],0) AS [22],ISNULL([23],15) AS [23],d AS date
FROM ( SELECT d,h,num
FROM x4
) t PIVOT( SUM(num) FOR h IN ( [0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23] ) ) t
WHERE type IS NOT NULL
来看一下最终效果，只有1天的数据，可能看起来不是很直观。
本文的技术点有2个：
函数的使用
原文链接：https://www.f2er.com/mssql/63044.html