我这里有一个表,关于考研班的,结构如下
Class Student1 Student2 Student3 ....
数学班 A B C .....
英语班 E G B .....
历史班 A D H .....
....以上这个表数据很多,我想再写一张表,它的结构成如下形式:
Student Class1 Class2 Class3 ....
A 数学班 历史班
B 数学班 英语班
C 数学班
...用SQL语言该怎么写啊
Class Student1 Student2 Student3 ....
数学班 A B C .....
英语班 E G B .....
历史班 A D H .....
....以上这个表数据很多,我想再写一张表,它的结构成如下形式:
Student Class1 Class2 Class3 ....
A 数学班 历史班
B 数学班 英语班
C 数学班
...用SQL语言该怎么写啊
--> --> (Roy)生成測試數據
set nocount on;
if not object_id('Class') is null
drop table [Class]
Go
set nocount on;
Create table [Class]([Class] nvarchar(3),[Student1] nvarchar(1),[Student2] nvarchar(1),[Student3] nvarchar(1))
Insert [Class]
select N'数学班',N'A',N'B',N'C' union all
select N'英语班',N'E',N'G',N'B' union all
select N'历史班',N'A',N'D',N'H'
Go
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Class],[Student]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Class')
order by Colidexec (
'declare @s nvarchar(4000),@i int
select *,ID=identity(int,1,1) into # from ('+@s+')T order by [Class],[Student]
select top 1 @s='''',@i=count(1) from # group by [Student] order by count(1) desc
while @i>0
select @s='',[Class''+rtrim(@i)+'']=max(case when row=''+rtrim(@i)+'' then [Class] else '''''''' end)''+@s,@i=@i-1exec(''select [Student]''+@s+'' from (select *,row=(select count(1) from # where [Student]=a.[Student] and ID<=a.ID) from # a)T group by [Student]'')'
)Student Class1 Class2
------- ------ ------
A 历史班 数学班
B 数学班 英语班
C 数学班
D 历史班
E 英语班
G 英语班
H 历史班
set nocount on;
if not object_id('Class') is null
drop table [Class]
GoCreate table [Class]([Class] nvarchar(3),[Student1] nvarchar(1),[Student2] nvarchar(1),[Student3] nvarchar(1))
Insert [Class]
select N'数学班',N'A',N'B',N'C' union all
select N'英语班',N'E',N'G',N'B' union all
select N'历史班',N'A',N'D',N'H'
Go
with T
as(
select
[Class],[Student],row=row_number()over(partition by [Student] order by [Class],[Student] )
from
[Class]
unpivot
([Student] for StudentCol in([Student1],[Student2],[Student3]))T
)
select
[Student],
[Class1]=max(case when row=1 then [Class] else '' end),
[Class2]=max(case when row=2 then [Class] else '' end)
from T
group by [Student]
Student Class1 Class2
------- ------ ------
A 历史班 数学班
B 数学班 英语班
C 数学班
D 历史班
E 英语班
G 英语班
H 历史班
Create table [Class]([Class] nvarchar(3),[Student1] nvarchar(1),[Student2] nvarchar(1),[Student3] nvarchar(1))
Insert [Class]
select N'数学班',N'A',N'B',N'C' union all
select N'英语班',N'E',N'G',N'B' union all
select N'历史班',N'A',N'D',N'H'
貌似人力完成工作量大了点吧 我想让代码自己遍历班级成员并写到新的表中 而不是人工添加 另外新表就不用排序了 学生名字为关键字不分先后也可以 该怎么写代码呢?
-------
是指生成表Class?那是一个测试数据表,执行脚本的结果才时楼主要的
--
第一可是动态,05取行记录转为动态就可以了
Insert [Class]
select N'数学班',N'A',N'B',N'C' union all
select N'英语班',N'E',N'G',N'B' union all
select N'历史班',N'A',N'D',N'H'
这一段是生成的测试数据表?我这里有一个表,关于考研班的,结构如下
Class Student1 Student2 Student3 ....
数学班 A B C .....
英语班 E G B .....
历史班 A D H .....
....
GO
Insert [Class]
select N'数学班',N'A',N'B',N'C',N'x' union all
select N'英语班',N'E',N'G',N'B',N'V' union all
select N'历史班',N'A',N'D',N'H',N'Z'
GO
SELECT * FROM class
/*
数学班 A B C x
英语班 E G B V
历史班 A D H Z
*/
DECLARE @cnt INT,@sql VARCHAR(MAX),@fld VARCHAR(MAX),@i INT,@sts VARCHAR(max)
SELECT @sts=ISNULL(@sts+'+','') + name FROM sys.columns WHERE object_id=object_id('class') and name !='class'
SELECT @fld=ISNULL(@fld,'') + ',Class' + RTRIM(row_number() over(order by class)) + '=(SELECT class FROM class WHERE ' + @sts + ' LIKE ''%'' + st + ''%'' AND Class=''' + class + ''')' FROM classSET @sts=REPLACE(@sts,'+',' FROM class UNION SELECT ')CREATE TABLE #sts(st varchar)
EXEC('INSERT #sts SELECT ' + @sts+ ' from class')
SELECT @sql=ISNULL(@sql+' UNION ALL SELECT ',' SELECT ')+'学生=''' + st + '''' + @fld +' FROM #sts WHERE st=''' + st + '''' FROM #sts
EXEC(@sql)
/*
A 历史班 数学班 NULL
B NULL 数学班 英语班
C NULL 数学班 NULL
D 历史班 NULL NULL
E NULL NULL 英语班
G NULL NULL 英语班
H 历史班 NULL NULL
V NULL NULL 英语班
x NULL 数学班 NULL
Z 历史班 NULL NULL
*/
GO
DROP TABLE #sts
DROP TABLE class
GO同时也可参看查询题解一文最后一例题。http://blog.csdn.net/fcuandy/archive/2007/04/05/1552710.aspx