现在表中查出来如下:
VALUE ID
111 2
234 3
312 3
65 3
78 2
541 2
现在我需要把VALUE这一列根据ID的值分开,想得到的结果如下:
VALUE2 VALUE3
111 234
78 312
541 65
这个SQL语句怎么写啊?
环境是在sql server2000中
VALUE ID
111 2
234 3
312 3
65 3
78 2
541 2
现在我需要把VALUE这一列根据ID的值分开,想得到的结果如下:
VALUE2 VALUE3
111 234
78 312
541 65
这个SQL语句怎么写啊?
环境是在sql server2000中
max(case px when 1 then value else 0 end) value2,
max(case px when 2 then value else 0 end) value3
from
(
select * , px = (select count(1) from tb where id = t.id and value < t.value) + 1 from tb t
) m
group by id
from
(select * , px = (select count(1) from tb where id = t.id and value < t.value) + 1 from tb t) m,
(select * , px = (select count(1) from tb where id = t.id and value < t.value) + 1 from tb t) n
where m.px = n.px
insert into tb values(111 , 2 )
insert into tb values(234 , 3 )
insert into tb values(312 , 3 )
insert into tb values(65 , 3 )
insert into tb values(78 , 2 )
insert into tb values(541 , 2 )
goselect m.value value1 , n.value value2
from
(select * , px = (select count(1) from tb where id = 2 and id = t.id and value < t.value) + 1 from tb t where id = 2) m,
(select * , px = (select count(1) from tb where id = 3 and id = t.id and value < t.value) + 1 from tb t where id = 3) n
where m.px = n.px drop table tb/*value1 value2
----------- -----------
111 234
78 65
541 312(所影响的行数为 3 行)
*/
insert into tb values(111 , 2 )
insert into tb values(234 , 3 )
insert into tb values(312 , 3 )
insert into tb values(65 , 3 )
insert into tb values(78 , 2 )
insert into tb values(541 , 2 )
goselect m.value value1 , n.value value2 from
(select * ,px = row_number() over(partition by id order by value) from tb ) m ,
(select * ,px = row_number() over(partition by id order by value) from tb ) n
where m.id = 2 and n.id = 3 and m.px = n.px5drop table tb/*
value1 value2
----------- -----------
78 65
111 234
541 312(3 行受影响)*/