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

# 横向排序，还是有点问题？

asdw001 2007-12-05 11:00:24

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 text a1 a2 a3 b1 b2 b3
1 'ta' 0 1 9 0 1 9
2 'tc' 4 8 4 4 4 8
3 'tt' 7 3 3 3 3 7
4 'rt' 3 0 3 0 3 3
5 'gg' 7 3 6 3 6 7
6 'sr' 2 5 5 2 5 5
7 'qq' 4 5 9 4 5 9
8 'sd' 5 5 8 5 5 8
9 'vs' 5 0 4 0 4 5
10 'ws' 4 0 8 0 4 8
11 'rf' 9 3 4 3 4 9

--------------------------

create table tb(id int, a1 int, a2 int, a3 int , a4 int)
insert into tb values(1, 5 , 10, 23, 1 )
insert into tb values(2, 34, 56, 7 , 17)
insert into tb values(3, 14, 28, 77, 25)
go
--用个临时表
select * into tmp from
(
select id , a1 from tb
union all
select id , a2 a1 from tb
union all
select id , a3 a1 from tb
union all
select id , a4 a1 from tb
) t

select t1.* , t2.a1 b1,t2.a2 b2,t2.a3 b3,t2.a4 b4 from tb t1,
(
select id ,
max(case px when 1 then a1 else 0 end) a1,
max(case px when 2 then a1 else 0 end) a2,
max(case px when 3 then a1 else 0 end) a3,
max(case px when 4 then a1 else 0 end) a4
from
(
select px=(select count(1) from tmp where id = m.id and a1 < m.a1)+1 , * from tmp m
) t
group by id
) t2
where t1.id = t2.id

drop table tb,tmp

/*
id a1 a2 a3 a4 b1 b2 b3 b4
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 5 10 23 1 1 5 10 23
2 34 56 7 17 7 17 34 56
3 14 28 77 25 14 25 28 77

（所影响的行数为 3 行）
*/
--------------------------------------------------------

create table # (id int, a1 int, a2 int, a3 int, a4 int)
insert # select 1, 5, 10, 23, 1
insert # select 2, 34, 56, 7, 17
insert # select 3, 14, 28, 77, 25

go

select ID,[COL]=a1,row=1 into #1
from #
union all
select ID,a2,row=1 from #
union all
select ID,a3,row=1 from #
union all
select ID,a4,row=1 from #
order by ID,COL asc
go
declare @i int,@j int
update #1
set @i=case when ID=@j then @i+1 else 1 end,row=@i,@j=ID
go

declare @s nvarchar(2000)
set @s='select ID'
select @s=@s+',[b'+rtrim(row)+']=max(case when row='+rtrim(row)+' then COl else 0 end)'
from #1 group by Row
exec(@s+' from #1 group by ID')
ID b1 b2 b3 b4
----------- ----------- ----------- ----------- -----------
1 1 5 10 23
2 7 17 34 56
3 14 25 28 77
...全文
62 点赞 收藏 8

8 条回复

-狙击手- 2007-12-06

xmer108 2007-12-06

asdw001 2007-12-05

dawugui 2007-12-05
``````--搞定.
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
--用个临时表
select id1 = identity(int,1,1) , * into tmp from
(
select id , t , a1 from tb
union all
select id , t , a2 a1 from tb
union all
select id , t , a3 a1 from tb
) t
order by id , a1

select t1.* , t2.a1 b1,t2.a2 b2,t2.a3 b3 from tb t1,
(
select id ,
max(case px when 1 then a1 else 0 end) a1,
max(case px when 2 then a1 else 0 end) a2,
max(case px when 3 then a1 else 0 end) a3
from
(
select px=(select count(1) from tmp where (id = m.id and a1 < m.a1) or ((id = m.id and a1 = m.a1 and id1 < m.id1)))+1 , * from tmp m
) t
group by id
) t2
where t1.id = t2.id

drop table tb,tmp

/*
id          t                                                                                                                                                                                                                                                                a1          a2          a3          b1          b2          b3
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ----------- ----------- -----------
1           ta                                                                                                                                                                                                                                                               0           1           9           0           1           9
2           tc                                                                                                                                                                                                                                                               4           8           4           4           4           8
3           tt                                                                                                                                                                                                                                                               7           3           3           3           3           7
4           rt                                                                                                                                                                                                                                                               3           0           3           0           3           3
5           gg                                                                                                                                                                                                                                                               7           3           6           3           6           7
6           sr                                                                                                                                                                                                                                                               2           5           5           2           5           5
7           qq                                                                                                                                                                                                                                                               4           5           9           4           5           9
8           sd                                                                                                                                                                                                                                                               5           5           8           5           5           8
9           vs                                                                                                                                                                                                                                                               5           0           4           0           4           5
10          ws                                                                                                                                                                                                                                                               4           0           8           0           4           8
11          rf                                                                                                                                                                                                                                                               9           3           4           3           4           9

（所影响的行数为 11 行）

*/``````

``````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

select   ID,[COL]=a1,row=1   into   #1
from   tb
union   all
select   ID,a2,row=1   from   tb
union   all
select   ID,a3,row=1   from   tb
order   by   ID,COL   asc
go
declare   @i   int,@j   int
update   #1
set   @i=case   when   ID=@j   then   @i+1   else   1   end,row=@i,@j=ID
go

declare   @s   nvarchar(2000)
set   @s='select   ID'
select   @s=@s+',[b'+rtrim(row)+']=max(case   when   row='+rtrim(row)+'   then   COl   else   0   end)'
from   #1   group   by   Row
exec(@s+'  into ##T				---生成临时表
from   #1   group   by   ID')

go
select
t.*,t2.b1,b2,b3
from
tb t
join
##T  t2 on t.ID=t2.ID

``````

dawugui 2007-12-05

MS-SQL Server

3.2w+

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