有如下表的样本code value
_____________001 a
001 b
002 a
002 r
003 d
003 g
__________________如何只使用一个select语句,输出:code value value
_________________________
001 a b
002 a r
003 d g
__________________________感觉是用select join,但本人解决不了,恳求大侠赐教!
_____________001 a
001 b
002 a
002 r
003 d
003 g
__________________如何只使用一个select语句,输出:code value value
_________________________
001 a b
002 a r
003 d g
__________________________感觉是用select join,但本人解决不了,恳求大侠赐教!
max(case px when 1 then value else '' end) value1,
max(case px when 2 then value else '' end) value2
from
(
select * , px = (select count(1) from tb where code = t.code and value < t.value) + from tb t
) m
group by code
from tb
group by code
insert into tb values('001' , 'a')
insert into tb values('001' , 'b')
insert into tb values('002' , 'a')
insert into tb values('002' , 'r')
insert into tb values('003' , 'd')
insert into tb values('003' , 'g')
goselect code,
max(case px when 1 then value else '' end) value1,
max(case px when 2 then value else '' end) value2
from
(
select * , px = (select count(1) from tb where code = t.code and value < t.value) + 1 from tb t
) m
group by codedrop table tb/*
code value1 value2
---------- ---------- ----------
001 a b
002 a r
003 d g(所影响的行数为 3 行)*/
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (code varchar(3),value varchar(1))
insert into #T
select '001','a' union all
select '001','b' union all
select '002','a' union all
select '002','r' union all
select '003','d' union all
select '003','g'
select code,max(case cn when 1 then value end)value, max(case cn when 2 then value end)value from
(
select *,cn=(select count(1) from #T where code=a.code and value<=a.value) from #T a
) t
group by code/*
code value value
---- ----- -----
001 a b
002 a r
003 d g
*/
from (
select * from ta a where not exists(select 1 from ta where a.code = code and value > a.value)
) b left join (
select * from ta a where not exists(select 1 from ta where a.code = code and value < a.value)
) c on b.code = c.code
create table #(code varchar(10),[value] varchar(10))
insert into # select '001','a'
insert into # select '001','b'
insert into # select '002','a'
insert into # select '002','r'
insert into # select '003','d'
insert into # select '003','g'select a.code,a.value as value1,b.value as value2 from
(
select code,[value] from
(
select *,num=(row_number() over (partition by code order by [value])) from #
) a
where num=1
) a,
(
select code,[value] from
(
select *,num=(row_number() over (partition by code order by [value])) from #
) a
where num=2
) b
where a.code=b.code/*
code value1 value2
---------- ---------- ----------
001 a b
002 a r
003 d g(3 行受影响)*/