一条统计sql语句 求救
数据库中表中存的记录格式 物品类型并不是只有这三种，是在一个表中存的。人员也是不确定的。
焊接人员 接线人员 物品类型
小李 小王 大柜子
小李 小王 中柜子
小王 小孙 中柜子
小孙 小李 大柜子
小孙 小王 小柜子
想得到的统计结果是
工作人员 工作内容 大柜子 中柜子 小柜子 合计
小李 焊接 1 1 0 2
接线 1 0 0 1
小王 焊接
接线
[解决办法]
--行列互转--1、行换列if object_id('Class') is not null drop table ClassGoCreate table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)Insert Classselect N'张三',N'语文',78 union allselect N'张三',N'数学',87 union allselect N'张三',N'英语',82 union allselect N'张三',N'物理',90 union allselect N'李四',N'语文',65 union allselect N'李四',N'数学',77 union allselect N'李四',N'英语',65 union allselect N'李四',N'物理',85 Go--2000方法:--动态:declare @s nvarchar(4000)set @s=''Select @s=@s+','+quotename([Course])+'=max(case when [Course]=' +quotename([Course],'''')+' then [Score] else 0 end)'from Class group by[Course]--select @sexec('select [Student]'+@s+' from Class group by [Student]')--生成静态:select [Student], [数学]=max(case when [Course]='数学' then [Score] else 0 end), [物理]=max(case when [Course]='物理' then [Score] else 0 end), [英语]=max(case when [Course]='英语' then [Score] else 0 end), [语文]=max(case when [Course]='语文' then [Score] else 0 end) from Class group by [Student]GO--动态:declare @s nvarchar(4000)Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]select @sexec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')--生成静态:select * from Class pivot (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b--生成格式:/*Student 数学 物理 英语 语文------- ----------- ----------- ----------- -----------李四 77 85 65 65张三 87 90 82 78(2 行受影响)*/go--加上总成绩(学科平均分)--2000方法:--动态:declare @s nvarchar(4000)set @s=''Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+'then [Score] else 0 end)'from Class group by[Course]exec('select [Student]'+@s+',[总成绩]=sum([Score]) from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态:select [Student], [数学]=max(case when [Course]='数学' then [Score] else 0 end), [物理]=max(case when [Course]='物理' then [Score] else 0 end), [英语]=max(case when [Course]='英语' then [Score] else 0 end), [语文]=max(case when [Course]='语文' then [Score] else 0 end), [总成绩]=([Score]) --加多一列(学科平均分用avg([Score]))from Class group by [Student]go--2005方法:动态:declare @s nvarchar(4000)Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a pivot (max([Score]) for [Course] in('+@s+'))b ')--生成静态:select [Student],[数学],[物理],[英语],[语文],[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])pivot (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/*Student 数学 物理 英语 语文 总成绩------- ----------- ----------- ----------- ----------- -----------李四 77 85 65 65 292张三 87 90 82 78 337(2 行受影响)*/go--2、列转行 if not object_id('Class') is null drop table ClassGoCreate table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)Insert Classselect N'李四',77,85,65,65 union allselect N'张三',87,90,82,78Go--2000:动态:declare @s nvarchar(4000)select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all+',[Score]='+quotename(Name)+' from Class'from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列order by Colidexec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态:select * from (select [Student],[Course]='数学',[Score]=[数学] from Class union all select [Student],[Course]='物理',[Score]=[物理] from Class union all select [Student],[Course]='英语',[Score]=[英语] from Class union all select [Student],[Course]='语文',[Score]=[语文] from Class)t order by [Student],[Course]go--2005:动态:declare @s nvarchar(4000)select @s=isnull(@s+',','')+quotename(Name)from syscolumns where ID=object_id('Class') and Name not in('Student') order by Colidexec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')goselect Student,[Course],[Score] from Class unpivot ([Score] for [Course] in([数学],[物理],[英语],[语文]))b生成格式:/*Student Course Score------- ------- -----------李四 数学 77李四 物理 85李四 英语 65李四 语文 65张三 数学 87张三 物理 90张三 英语 82张三 语文 78*/
[解决办法]
--焊接人员 接线人员 物品类型declare @sql varchar(max)select * into #tbfrom( select 焊接人员 [人员],物品类型,'焊接' as flag from tb union all select 接线人员 [人员],物品类型,'接线' as flag from tb) tset @sql = 'select 人员,flag,'select @sql = @sql + ',sum(case when 物品类型='''+物品类型+''' then 1 else 0 end) ['+物品类型+']'from #tbgroup by 物品类型select @sql = @sql + ',count(*) as 合计 from #tb group by 人员,flag'exec(@sql)
[解决办法]
--数据库中表中存的记录格式 物品类型并不是只有这三种，是在一个表中存的。人员也是不确定的。--> 测试数据：[tbl]if object_id('[tbl]') is not null drop table [tbl]create table [tbl]([焊接人员] varchar(4),[接线人员] varchar(4),[物品类型] varchar(6))insert [tbl]select '小李','小王','大柜子' union allselect '小李','小王','中柜子' union allselect '小王','小孙','中柜子' union allselect '小孙','小李','大柜子' union allselect '小孙','小王','小柜子'create table #tt([工作人员 ] varchar(10),物品类型 varchar(10),工作内容 varchar(10))insert #ttselect 焊接人员 [工作人员 ],物品类型,'焊接' as 工作内容 from tbl union all select 接线人员 [工作人员 ],物品类型,'接线' as 工作内容 from tblselect * from #ttdeclare @str varchar(max)set @str=''select @str=@str+','+物品类型+'=sum(case when 物品类型='+quotename(物品类型,'''')+' then 1 else 0 end)' from #tt group by 物品类型exec('select [工作人员],[工作内容]'+@str+' ,count(*) as 合计 from #tt group by [工作人员 ],工作内容order by [工作人员]')/*工作人员 工作内容 小柜子 中柜子 大柜子 合计小李 焊接 0 1 1 2小李 接线 0 0 1 1小孙 焊接 1 0 1 2小孙 接线 0 1 0 1小王 焊接 0 1 0 1小王 接线 1 1 1 3*/