这条SQL统计语句该如何写？

2010-11-10 08:33:42 270 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

统计报表 -- sql统计语句需要数据统计页面，肯定需要导出数据，于是，边学边写，完成了一段sql~ 最早的版本是这样的：分三条sql查出三种不同的状态的记录数(总记录，未支付，已支付) SELECT COUNT(*) AS RecordCount FROM t_record WHERE CreateTime &gt;= '2019-01-01 00:00:00' AND CreateTime &lt; '2019-0... 阅读详情

有如下一张表：

id 购入量消耗量商品类型姓名
--------------------------------------------
204 12 28.45 型号A 张三
205 11 34.93 型号A 张三
206 2 36.13 型号A 张三
207 45 27.25 型号B 张三
208 101 40.8 型号B 张三
209 2 33.15 型号C 张三
210 45 29.75 型号A 李四
211 11 37.67 型号A 李四
212 78 37.22 型号A 李四
213 0 31.56 型号B 李四
214 0 24.81 型号C 李四
215 11.2 23.71 型号C 李四
218 0 37.08 型号B 王五
219 23.6 34.9 型号C 王五
220 22 23.33 型号C 王五
221 145 30.14 型号A 赵六
222 214 29.74 型号A 赵六
223 2.3 19.77 型号C 赵六
224 21 27.07 型号C 赵六
225 87.2 36.08 型号B 赵六

我想统计出这样一个结果：

姓名型号A购入量型号A消耗量型号B购入量型号B消耗量型号C购入量型号C消耗量
------------------------------------------------------------------------------
张三 XX XX XX XX XX XX
李四 XX XX XX XX XX XX
王五 XX XX XX XX XX XX
赵六 XX XX XX XX XX XX

要求列出所有人的数据，如果某个人没有数据就用0代替，商品类型就这三种 MSSQL 2000

还有，上面说的商品类型是固定的个数，如果商品类型是不固定的呢？比如可能有5种，或8种商品类型，还能按上面的格式统计出来吗？就是在结果集中自动生成对应商品类型的列（购入和消耗）

...全文

【SQL Server】统计信息实战+实用SQL语句本文重点介绍实际统计信息的运用，包括如何查询统计信息，统计信息相关的重要指标，如何找到系统中需要更新的统计信息，以及统计信息语句监控SQL语句运行情况等阅读详情

heikeyanxi

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

htl258_Tony 2010-11-10

打赏
举报

--> 生成测试数据表: [tb]

IF OBJECT_ID('[tb]') IS NOT NULL

	DROP TABLE [tb]

GO

CREATE TABLE [tb] ([id] [int],[购入量] [numeric](13,1),[消耗量] [numeric](14,2),[商品类型] [nvarchar](10),[姓名] [nvarchar](10))

INSERT INTO [tb]

SELECT '204','12','28.45','型号A','张三' UNION ALL

SELECT '205','11','34.93','型号A','张三' UNION ALL

SELECT '206','2','36.13','型号A','张三' UNION ALL

SELECT '207','45','27.25','型号B','张三' UNION ALL

SELECT '208','101','40.8','型号B','张三' UNION ALL

SELECT '209','2','33.15','型号C','张三' UNION ALL

SELECT '210','45','29.75','型号A','李四' UNION ALL

SELECT '211','11','37.67','型号A','李四' UNION ALL

SELECT '212','78','37.22','型号A','李四' UNION ALL

SELECT '213','0','31.56','型号B','李四' UNION ALL

SELECT '214','0','24.81','型号C','李四' UNION ALL

SELECT '215','11.2','23.71','型号C','李四' UNION ALL

SELECT '218','0','37.08','型号B','王五' UNION ALL

SELECT '219','23.6','34.9','型号C','王五' UNION ALL

SELECT '220','22','23.33','型号C','王五' UNION ALL

SELECT '221','145','30.14','型号A','赵六' UNION ALL

SELECT '222','214','29.74','型号A','赵六' UNION ALL

SELECT '223','2.3','19.77','型号C','赵六' UNION ALL

SELECT '224','21','27.07','型号C','赵六' UNION ALL

SELECT '225','87.2','36.08','型号B','赵六'



--SELECT * FROM [tb]



-->SQL查询如下:



DECLARE @S VARCHAR(8000)

SELECT @S=ISNULL(@S,'')

	     +',MAX(CASE [商品类型] WHEN ''' + [商品类型] + ''' THEN [购入量] ELSE 0 END) [' + 商品类型 + '购入量]'

	     +',MAX(CASE [商品类型] WHEN ''' + [商品类型] + ''' THEN [消耗量] ELSE 0 END) [' + 商品类型 + '消耗量]'	

FROM tb

GROUP BY [商品类型]

ORDER BY MIN(ID)

EXEC('SELECT 姓名'+@S+' FROM tb GROUP BY 姓名') 

/*

姓名         型号A购入量                                  型号A消耗量                                  型号B购入量                                  型号B消耗量                                  型号C购入量                                  型号C消耗量

---------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------

李四         78.0                                    37.67                                   0.0                                     31.56                                   11.2                                    24.81

王五         0.0                                     0.00                                    0.0                                     37.08                                   23.6                                    34.90

张三         12.0                                    36.13                                   101.0                                   40.80                                   2.0                                     33.15

赵六         214.0                                   30.14                                   87.2                                    36.08                                   21.0                                    27.07



(4 行受影响)

*/

fpzgm 2010-11-10

打赏
举报



--固定的话用下面的

select

[姓名] 

 ,sum(case 商品类型 when '型号A' then 购入量 else 0 end) as [型号A购入量]

 ,sum(case 商品类型 when '型号A' then 消耗量 else 0 end) as [型号A消耗量]

 ,sum(case 商品类型 when '型号B' then 购入量 else 0 end) as [型号B购入量]

 ,sum(case 商品类型 when '型号B' then 消耗量 else 0 end) as [型号B消耗量]

 ,sum(case 商品类型 when '型号C' then 购入量 else 0 end) as [型号C购入量]

 ,sum(case 商品类型 when '型号C' then 消耗量 else 0 end) as [型号C消耗量]

from [table]

group by [姓名]



--型号不固定用1楼

chen8410 2010-11-10

打赏
举报

select

[姓名] 

 ,sum(case 商品类型 when '型号A' then 购入量 else 0 end) as 型号A购入量

 ,sum(case 商品类型 when '型号A' then 消耗量 else 0 end) as 型号A消耗量

 ,sum(case 商品类型 when '型号B' then 购入量 else 0 end) as 型号B购入量

 ,sum(case 商品类型 when '型号B' then 消耗量 else 0 end) as 型号B消耗量

 ,sum(case 商品类型 when '型号C' then 购入量 else 0 end) as 型号C购入量

 ,sum(case 商品类型 when '型号C' then 消耗量 else 0 end) as 型号C消耗量

from [表]

group by [姓名]

昵称被占用了 2010-11-10

打赏
举报

不固定如1楼

昵称被占用了 2010-11-10

打赏
举报

select

  姓名 

 ,sum(case when 商品类型 = '型号A' then 购入量 else 0 end) as 型号A购入量

 ,sum(case when 商品类型 = '型号A' then 消耗量 else 0 end) as 型号A消耗量

 ,sum(case when 商品类型 = '型号B' then 购入量 else 0 end) as 型号B购入量

 ,sum(case when 商品类型 = '型号B' then 消耗量 else 0 end) as 型号B消耗量

 ,sum(case when 商品类型 = '型号C' then 购入量 else 0 end) as 型号C购入量

 ,sum(case when 商品类型 = '型号C' then 消耗量 else 0 end) as 型号C消耗量

from tab

group by 姓名

「已注销」 2010-11-10

打赏
举报

CREATE TABLE #tp

(

	id  INT,

	购入量  int,

	消耗量 DECIMAL(10,2),

	商品类型 NVARCHAR(10),

	姓名 NVARCHAR(10)	

)



INSERT INTO #tp

SELECT 204, 12, 28.45, N'型号A', N'张三'

UNION ALL

SELECT 205, 11, 34.93, N'型号A', N'张三'

UNION ALL

SELECT 206, 2, 36.13, N'型号A', N'张三'

UNION ALL

SELECT 207, 45, 27.25, N'型号B', N'张三'

UNION ALL

SELECT 208, 101, 40.8, N'型号B', N'张三'

UNION ALL

SELECT 209, 2, 33.15, N'型号C', N'张三'

UNION ALL

SELECT 210, 45, 29.75, N'型号A', N'李四'

UNION ALL

SELECT 211 ,11, 37.67 , N'型号A', N'李四'

UNION ALL

SELECT 212 ,78 ,37.22, N'型号A', N'李四'

UNION ALL

SELECT 213 ,0 ,31.56, N'型号B', N'李四'

UNION ALL

SELECT 214 ,0 ,24.81, N'型号C', N'李四'

UNION ALL

SELECT 215 ,11.2, 23.71, N'型号C', N'李四'

UNION ALL

SELECT 218, 0, 37.08, N'型号B', N'王五'

UNION ALL

SELECT 219 ,23.6, 34.9 , N'型号C', N'王五'

UNION ALL

SELECT 220, 22 ,23.33, N'型号C', N'王五'

UNION ALL

SELECT 221, 145, 30.14, N'型号A', N'赵六'

UNION ALL

SELECT 222 ,214 ,29.74, N'型号A', N'赵六'

UNION ALL

SELECT 223, 2.3, 19.77, N'型号C', N'赵六'

UNION ALL

SELECT 224, 21 ,27.07, N'型号C', N'赵六'

UNION ALL

SELECT 225 ,87.2 ,36.08, N'型号B', N'赵六'



SELECT * FROM #tp t



--SQL SERVER 2000 动态SQL

DECLARE @SQL NVARCHAR(4000)

SET @SQL = N'SELECT M.*,N.* FROM (SELECT [姓名] '

SELECT @SQL = @SQL + N' , MAX(CASE [商品类型] WHEN N''' + [商品类型] + N''' THEN [购入量] ELSE 0 END) [' + 商品类型 + ']'

FROM (SELECT DISTINCT [商品类型] FROM #TP) AS A

SET @SQL = @SQL + N' FROM #TP GROUP BY [姓名])M LEFT JOIN ('

SET @SQL = @SQL + N'SELECT [姓名] '

SELECT @SQL = @SQL + N' , MAX(CASE [商品类型] WHEN N''' + [商品类型] + N''' THEN [消耗量] ELSE 0 END) [' + 商品类型 + ']'

FROM (SELECT DISTINCT [商品类型] FROM #TP) AS A

SET @SQL = @SQL + N' FROM #TP GROUP BY [姓名])N ON M.[姓名]=N.[姓名]'



EXEC(@SQL) 



姓名         型号A         型号B         型号C         姓名         型号A                                     型号B                                     型号C

---------- ----------- ----------- ----------- ---------- --------------------------------------- --------------------------------------- ---------------------------------------

张三         12          101         2           张三         36.13                                   40.80                                   33.15

李四         78          0           11          李四         37.67                                   31.56                                   24.81

王五         0           0           23          王五         0.00                                    37.08                                   34.90

赵六         214         87          21          赵六         30.14                                   36.08                                   27.07



(4 row(s) affected)

这条SQL统计语句该如何写？

相关推荐