• 主页
• 基础类
• 应用实例
• 新技术前沿

# 纵向排列问题?

asdw001 2007-12-24 09:46:39
``````

create table tb(id int,t text,a1 int,a2 int,a3 int)
insert into tb values(1,'ta',0,1,9)
insert into tb values(2,'tc',4,8,4)
insert into tb values(3,'tt',7,3,3)
insert into tb values(4,'rt',3,0,3)
insert into tb values(5,'gg',7,3,6)
insert into tb values(6,'sr',2,5,5)
insert into tb values(7,'qq',4,5,9)
insert into tb values(8,'sd',5,5,8)
insert into tb values(9,'vs',5,0,4)
insert into tb values(10,'ws',4,0,8)
insert into tb values(11,'rf',9,3,4)
go

id      t       w
1	ta	0
1	ta	1
1	ta	9
2	tc	4
2	tc	8
2	tc	4
3	tt	7
3	tt	3
3	tt	3
4	rt	3
4	rt	0
4	rt	3
5	gg	7
5	gg	3
5	gg	6
6	sr	2
6	sr	5
6	sr	5
7	qq	4
7	qq	5
7	qq	9
8	sd	5
8	sd	5
8	sd	8
9	vs	5
9	vs	0
9	vs	4
10	ws	4
10	ws	0
10	ws	8
11	rf	9
11	rf	3
11	rf	4

/*下面的代码有点问题，在处理第上面id为第6,8,10个数据时,顺序作了改变。

select * from
(
select id , t , a1 w from tb
union all
select id , t , a2 w from tb
union all
select id , t , a3 w from tb
) t
order by id
*/
``````
...全文
215 点赞 收藏 15

15 条回复

paula2008 2007-12-26

pl1069 2007-12-26

shuihao2004 2007-12-26

terrywangbuaa 2007-12-25

lsyyfj 2007-12-25

laowang2 2007-12-25
up

asdw001 2007-12-24

--在生成时，加多一个排序列row

--

``````
create table tb(id int,t text,a1 int,a2 int,a3 int)
insert into tb values(1,'ta',0,1,9)
insert into tb values(2,'tc',4,8,4)
insert into tb values(3,'tt',7,3,3)
insert into tb values(4,'rt',3,0,3)
insert into tb values(5,'gg',7,3,6)
insert into tb values(6,'sr',2,5,5)
insert into tb values(7,'qq',4,5,9)
insert into tb values(8,'sd',5,5,8)
insert into tb values(9,'vs',5,0,4)
insert into tb values(10,'ws',4,0,8)
insert into tb values(11,'rf',9,3,4)

go

declare @s nvarchar(1000)
select @s=isnull(@s+' union all ','')+'select id,t,[col]='+quotename(Name)+',row='+rtrim(colid)+' from Tb '
from syscolumns
where Name not in('id','t') and id=object_id('tb') order by colid

exec(@s+' order by ID asc,row asc')

--
select
id,t,col
from
(select id,t,[col]=[a1],row=3 from Tb
union all
select id,t,[a2],row=4 from Tb
union all
select id,t,[a3],row=5 from Tb
)T
order by ID asc,row asc

/*
1	ta	0
1	ta	1
1	ta	9
2	tc	4
2	tc	8
2	tc	4
3	tt	7
3	tt	3
3	tt	3
4	rt	3
4	rt	0
4	rt	3
5	gg	7
5	gg	3
5	gg	6
6	sr	2
6	sr	5
6	sr	5
7	qq	4
7	qq	5
7	qq	9
8	sd	5
8	sd	5
8	sd	8
9	vs	5
9	vs	0
9	vs	4
10	ws	4
10	ws	0
10	ws	8
11	rf	9
11	rf	3
11	rf	4

*/``````

asdw001 2007-12-24

``````
create table A(id char(3),        num1 int,    num2 int,    num3  int,   num4 int)
insert A select '001',         80,         90,         50,         60
insert A select '002',         84,         70,         60,         82

go

--SQL2005实现方法:
select
*
from
A
unpivot
(num for col in([num1],[num2],[num3],[num4]))T2
)tmp

--SQL2000实现:

---调用系统表动态生态
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+' select ID,[num]='+quotename(Name,'''')+',Qty='+quotename(Name)+' from A'
from syscolumns
where Name!=N'ID' and ID=object_id('A')--表名A，不包含列名为ID的其它列
order by colid asc
exec(@s+' order by ID asc,[num] asc')

--生成的静态语句
select ID,[num]='num1',Qty=[num1] from A union all
select ID,[num]='num2',Qty=[num2] from A union all
select ID,[num]='num3',Qty=[num3] from A union all
select ID,[num]='num4',Qty=[num4] from A
order by ID asc,[num] asc

/*

ID   num  Qty
---- ---- -----------
001  num1 80
001  num2 90
001  num3 50
001  num4 60
002  num1 84
002  num2 70
002  num3 60
002  num4 82
------------------------------
*/

--动态方法:
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+' select ID,[num]='+quotename(Name)+' from A'
from syscolumns
where Name!=N'ID' and ID=object_id('A')
order by colid asc
exec(@s+' order by ID asc')

--生成的语句如下:

select ID,[num]=[num1] from A union all
select ID,[num]=[num2] from A union all
select ID,[num]=[num3] from A union all
select ID,[num]=[num4] from A
order by ID asc,[num] asc

/*
ID   num
---- -----------
001  80
001  90
001  50
001  60
002  82
002  60
002  70
002  84
*/

go

declare @s2 nvarchar(4000)
set @s2=''
select @s2=@s2+','+quotename(id)+'=sum(case when ID='+quotename(ID,'''')+' then Qty else 0 end)'
from A
group by ID

declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+' select ID,[num]='+quotename(Name,'''')+',Qty='+quotename(Name)+' from A'
from syscolumns
where Name!=N'ID' and ID=object_id('A')--表名A，不包含列名为ID的其它列
order by colid asc

---生成格式如下:

select
[num],
[001]=sum(case when ID='001' then Qty else 0 end),
[002]=sum(case when ID='002' then Qty else 0 end)
from
( select ID,[num]='num1',Qty=[num1] from A union all
select ID,[num]='num2',Qty=[num2] from A union all
select ID,[num]='num3',Qty=[num3] from A union all
select ID,[num]='num4',Qty=[num4] from A) T
group by [num]

/*
num  001         002
---- ----------- -----------
num1 80          84
num2 90          70
num3 50          60
num4 60          82

（所影响的行数为 4 行）

*/

---drop table A``````

wzy_love_sly 2007-12-24
,0,1,9还以为是个串

asdw001 2007-12-24

liangCK 2007-12-24
select * from
(
select id , t , a1 w from tb
union all
select id , t , a2 w from tb
union all
select id , t , a3 w from tb
) t
order by id ,w

wzy_love_sly 2007-12-24

MS-SQL Server

3.2w+

MS-SQL Server相关内容讨论专区