导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

如何实现如下功能的sql

singer_101 2007-12-11 03:14:45
数据库记录
id Vote
44 -2
51 -3
51 1
52 -1

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

每个ID这只可能有 1-2 行记录
...全文
70 点赞 收藏 8
写回复
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
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
回复
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
回复
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.id
回复
kk19840210 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 行受影响)



回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告