3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
如何实现如下功能的sql
数据库记录
id Vote
44 -2
51 -3
51 1
52 -1
输出
id -vote +vote
44 -2
51 -3 1
52 -1
每个ID这只可能有 1-2 行记录
id Vote
44 -2
51 -3
51 1
52 -1
输出
id -vote +vote
44 -2
51 -3 1
52 -1
每个ID这只可能有 1-2 行记录
...全文
请发表友善的回复…
发表回复
East271536394 2008-04-22
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
id -vote +vote
----------- ----------- -----------
44 -2 NULL
51 -3 1
52 -1 NULL
lingyunfeipu 2008-04-22
收藏，jf
andysun88 2007-12-11
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
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
fwacky 2007-12-11
/*
id vote1 vote2
----------- ----------- -----------
44 -2 NULL
51 -3 1
52 -1 NULL
（所影响的行数为 3 行）
*/
fwacky 2007-12-11
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
dawugui 2007-12-11
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 行）
*/dawugui 2007-12-11
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.idkk19840210 2007-12-11
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 行受影响)