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

heikeyanxi 2010-11-10 08:33:42
有如下一张表：

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种商品类型，还能按上面的格式统计出来吗？就是在结果集中自动生成对应商品类型的列（购入和消耗）




...全文
203 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
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 Server数据库基础到精通课程
本课程讲解从最开始的 创建数据库、创建数据表、已经数据的写入、查询、修改更新、删除，字段运算、字段运算统计、分类汇总、数据分析、条件判断，条件筛选及一些常用的SQL数据库语句，变量定义，字段约束、等等，...
SQL统计语句
1、SQL COUNT() 函数 ：SQL COUNT() 函数 2、SQL SUM() 函数：SQL SUM() 函数 3、GROUP BY 语句：SQL GROUP BY 语句
统计每天、每月每天、每年每月的SQL语句怎么写？
关键就在date_format（）函数的使用。 定义：DATE_FORMAT() 函数用于以不同的...# 统计当天发送数和计费数 select count(0) as sendCount,IFNULL(sum(tstd.task_status=1),0) as billingCount from t_sms_task AS tst
报表统计sql应该如何写？
如果你遇到如下的场景，查询某个人的补贴汇总情况，会如何通过sql完成任务呢？ 二.思路 思路1： 将总计行最后通过union all 拼接起来 思路2： 使用数据库自带的函数with rollup，思路二也是别人提供的 三.具体解决...
mysql语句统计总数_一条sql语句实现统计查询_MySQL
bitsCN.com一条sql语句实现统计查询如图：程序员在进行如下的统计时，现在提供两种实现方案：方案一：运用 SEKECT CASE WHENEXPLAIN SELECT count(*) AS '总数', count( CASE oup.status WHEN '1' THEN oup.id ...
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章