34,838
社区成员
发帖
与我相关
我的任务
分享横向排序，还是有点问题？
重列原始数据表a，代码如下：
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
要求对数据表a进行横向排序。id,t字段不进行排序，只
对a1,a2,a3字段按从小到大进行横向排序，所排的结果输
入到表b中。表b的结构如下：
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
下面贴上两个有点问题代码，原程序都测试通过了。
只不过采用我上面表a的数据时，当然没有text字段，并
增加了a4字段，值全为0，进行测试时，出现了数据不对
并排列混乱id也混乱的情况。但原程序测试都对的，不
知道问题出在哪儿？
--------------------------
第一个程序如下：
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
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
要求对数据表a进行横向排序。id,t字段不进行排序，只
对a1,a2,a3字段按从小到大进行横向排序，所排的结果输
入到表b中。表b的结构如下：
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
下面贴上两个有点问题代码，原程序都测试通过了。
只不过采用我上面表a的数据时，当然没有text字段，并
增加了a4字段，值全为0，进行测试时，出现了数据不对
并排列混乱id也混乱的情况。但原程序测试都对的，不
知道问题出在哪儿？
--------------------------
第一个程序如下：
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
...全文
请发表友善的回复…
发表回复
-狙击手- 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 行）
*/
中国风 2007-12-05
- 打赏
- 举报
用2005可用unpivot/row_number得出结果。。2000用以上方法
中国风 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 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
- 打赏
- 举报
如果a1,a2,a3,其中两个有重复,得用临时表才行了.
中国风 2007-12-05
- 打赏
- 举报
好像有偶的回复..看看
