一条SQL语句整了半天 实在出不来 求解
一条查询语句
S_DD表如下
ID uname S_name S_time S_add
1 小明 圆珠笔 2012/5/10 15:17:00 xxxx小区
2 王明 圆珠笔 2012/5/10 15:17:00 人民路
3 小红 钢笔 2012/5/11 12:17:00 京良路
4 王华 圆珠笔 2012/5/11 12:17:00 双回路
要得到的结果如下
S_name S_time S_name1 S_add1 S_name2 S_add2
圆珠笔 2012/5/10 15:17:00 小明 xxxx小区 王明 人民路
钢笔 2012/5/11 12:17:00 小红 京良路 NULL NULL
圆珠笔 2012/5/11 12:17:00 王华 双回路 NULL NULL
这样的SQL语句怎么写？实在写不出来了
[解决办法]
--行列互转--摘自中国风博客，引用请标明内容来源--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*/
[解决办法]
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'S_DD')BEGIN DROP TABLE S_DDENDGOCREATE TABLE S_DD( ID INT, uname VARCHAR(100), S_name VARCHAR(100), S_time DATETIME, S_add VARCHAR(100))GOINSERT INTO S_DDSELECT 1, '小明', '圆珠笔', '2012/5/10 15:17:00', 'xxxx小区' UNIONSELECT 2, '王明', '圆珠笔', '2012/5/10 15:17:00', '人民路' UNIONSELECT 3, '小红', '钢笔', '2012/5/11 12:17:00', '京良路' UNIONSELECT 4, '王华', '圆珠笔', '2012/5/11 12:17:00', '双回路'GOWITH tba AS(SELECT uname,s_name,s_time,S_add,RN=ROW_NUMBER() OVER (PARTITION BY S_Name,S_Time ORDER BY ID) FROM S_DD)SELECT A.S_name,A.S_time,A.uname,A.S_add,B.uname,B.S_addFROM (SELECT * FROM tba WHERE RN % 2 = 1)AS A LEFT OUTER JOIN (SELECT * FROM tba WHERE RN % 2 = 0) AS B ON A.RN = B.RN - 1 AND A.RN % 2 = 1 AND B.RN % 2 = 0 AND A.S_name = B.S_name AND A.S_time = B.S_timeS_name S_time uname S_add uname S_add钢笔 2012-05-11 12:17:00.000 小红 京良路 NULL NULL圆珠笔 2012-05-10 15:17:00.000 小明 xxxx小区 王明 人民路圆珠笔 2012-05-11 12:17:00.000 王华 双回路 NULL NULL