select id,
max(case px when 1 then value else '0' end) 'value1',
max(case px when 2 then value else '0' end) 'value2',
max(case px when 3 then value else '0' end) 'value3'
from
(
select px=(select count(1) from tb where id=a.id and value<a.value)+1 , * from tb a
) t
group by id
order by id
max(case px when 1 then value else '0' end) 'value1',
max(case px when 2 then value else '0' end) 'value2',
max(case px when 3 then value else '0' end) 'value3'
from
(
select px=(select count(1) from tb where id=a.id and value<a.value)+1 , * from tb a
) t
group by id
order by id
insert into tb values(1, 'aa' )
insert into tb values(1, 'bb' )
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
goselect id,
max(case px when 1 then value else '0' end) 'value1',
max(case px when 2 then value else '0' end) 'value2',
max(case px when 3 then value else '0' end) 'value3'
from
(
select px=(select count(1) from tb where id=a.id and value<a.value)+1 , * from tb a
) t
group by id
order by iddrop table tb/*
id value1 value2 value3
----------- ---------- ---------- ----------
1 aa bb 0
2 aaa bbb ccc(所影响的行数为 2 行)
*/