如何实现如下功能的sql

[singer_101]

数据库记录
id Vote
44 -2
51 -3
51 1
52 -1

输出
id -vote +vote
44 -2
51 -3 1
52 -1

每个ID这只可能有 1-2 行记录

[kk19840210]

create table #(id int,vote int)

insert into # values(44,-2)

insert into # values(51,-3)

insert into # values(51,1)

insert into # values(52,-1)



select id,max(case  when vote<0 then vote else null end) as '-vote',max(case  when vote>0 then vote else null end) as '+vote' from # group by id

id          -vote       +vote
----------- ----------- -----------
44          -2          NULL
51          -3          1
52          -1          NULL
警告: 聚合或其他 SET 操作消除了空值。

(3 行受影响)

[dawugui]

select isnull(t1.id , t2.id) id , t1.vote vote1 , t2.vote vote2 from
(select id , vote from tb where vote < 0) t1
full join
(select id , vote from tb where vote > 0) t2
on t1.id = t2.id

[dawugui]

create table  tb(id int,vote int)

insert into  tb values(44,-2)

insert into  tb values(51,-3)

insert into  tb values(51,1)

insert into  tb values(52,-1)

select isnull(t1.id , t2.id) id , t1.vote vote1 , t2.vote vote2 from
(select id , vote from tb where vote < 0) t1
full join
(select id , vote from tb where vote > 0) t2
on t1.id = t2.id
order by t1.id

drop table tb

/*
id          vote1       vote2       
----------- ----------- ----------- 
44          -2          NULL
51          -3          1
52          -1          NULL

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

[fwacky]

create table singer_101 
(id int,

vote int)
insert into singer_101 values(44,-2)

insert into singer_101 values(51,-3)

insert into singer_101 values(51,1)

insert into singer_101 values(52,-1)

select id, sum(case  when vote > 0 then vote else null end ) as '-vote' ,
sum(case  when vote < 0 then vote else null  end ) as '+vote'  from singer_101
group by id 

[fwacky]

/*
id          vote1       vote2       
----------- ----------- ----------- 
44          -2          NULL
51          -3          1
52          -1          NULL

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

[andysun88]

test

select id
,(select vote from test where vote < 0 and id=te.id) as '-vote'
,(select vote from test where vote > 0 and id=te.id) as '+vote'
from test te group by id

測試結果:
id -vote +vote
----------- ----------- -----------
44 -2 NULL
51 -3 1
52 -1 NULL

[lingyunfeipu]

收藏，jf

[East271536394]

select id,max(case when vote<0 then vote else null end) as '-vote',max(case when vote>0 then vote else null end) as '+vote' from # group by id

id -vote +vote
----------- ----------- -----------
44 -2 NULL
51 -3 1
52 -1 NULL