数据库环境: SERVER 2005

现有一个产品销售实时表,表数据如下:

如何统计全天各个时间段产品销量情况(sqlserver)

字段name是产品名称,字段type是销售类型,1表示售出,2表示退货,字段num是数量,字段ctime是操作时间。

要求:

  在一行中统计24小时内所有货物的销售(售出,退货)数据,把日期考虑在内。

分析:

  这实际上是行转列的一个应用,在进行行转列之前,需要补全24小时的所有数据。补全数据可以通过系统的数字辅助表

spt_values来实现,进行行转列时,根据type和处理后的ctime分组即可。

1.建表,导入数据

  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,2 ,'2015-08-10 18:21:10')  INSERT INTO snake VALUES(' 香烟A ', 1,5 ,'2015-08-10 20:21:10')  INSERT INTO snake VALUES(' 香烟B', 1,6 ,'2015-08-10 20:21:10')  INSERT INTO snake VALUES(' 香烟B', 2,9 ,'2015-08-10 20:21:10')  INSERT INTO snake VALUES(' 香烟C', 2,9 ,'2015-08-10 20:21:10')

2.补全24小时的数据

  /*枚举0-23自然数列*/  WITH  x0       AS ( SELECT  number AS h          FROM   master..spt_values          WHERE  type = 'P'              AND number >= 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.行转列

  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] ,        ISNULL([3], 7) AS [07] ,        ISNULL([8], 0) AS [08] ,        ISNULL([9], 0) AS [09] ,        ISNULL([10], 0) AS [10] ,        ISNULL([3], 11) AS [11] ,        ISNULL([12], 0) AS [12] ,        ISNULL([13], 0) AS [13] ,        ISNULL([14], 0) AS [14] ,        ISNULL([3], 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] ,        type ,        d AS date    FROM  ( SELECT  d ,              h ,              type ,              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天的数据,可能看起来不是很直观。

如何统计全天各个时间段产品销量情况(sqlserver)

本文的技术点有2个:

  1.利用数字辅助表补全缺失的记录

  2.pivot行转列函数的使用

以上内容是如何统计全天各个时间段产品销量情况()的全部内容,希望大家喜欢。

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注