首页 新闻 搜索 专区 学院

求个存储过程

0
悬赏园豆:50 [已解决问题] 解决于 2011-07-01 09:24

图片表 字段(图片ID,用户ID,投票票数)

视频表 字段(视频ID,用户ID,投票票数)

用户表  (用户ID,用户姓名)

要求每个用户的总票数(图片票数加上视频票数)

结果.

用户姓名 总票数

用户1    1000

用户2     2000

大漠孤烟。的主页 大漠孤烟。 | 初学一级 | 园豆:12
提问于:2011-06-30 16:36
< >
分享
最佳答案
0

create table #tb_User(ID int, UName nvarchar(50))
create table #tb_Picture(PID
int, UserID int, VoteNum int)
create table #tb_Video(VID
int, UserID int, VoteNum int)

insert into #tb_User select
1, 'User_A'
insert into #tb_User select
2, 'User_B'
insert into #tb_User select
3, 'User_C'
insert into #tb_User select
4, 'User_D'

insert into #tb_Picture select
1, 1, 100
insert into #tb_Picture select
2, 1, 100
insert into #tb_Picture select
3, 2, 50
insert into #tb_Picture select
4, 3, 20
insert into #tb_Picture select
4, 4, 50

insert into #tb_Video select
1, 1, 200
insert into #tb_Video select
2, 2, 20
insert into #tb_Video select
3, 2, 100
insert into #tb_Video select
4, 3, 150

select U.ID, U.UName, ISNULL(P.VoteNum,
0)+ISNULL(V.VoteNum, 0) as TotalNum from #tb_User U
left join
(select SUM(VoteNum)
as VoteNum, UserID from #tb_Picture group by UserID) P
on U.ID
=P.UserID
left join
(select SUM(VoteNum)
as VoteNum, UserID from #tb_Video group by UserID) V
on U.ID
=V.UserID

drop table #tb_User
drop table #tb_Picture
drop table #tb_Video

收获园豆:50
镆铘 | 菜鸟二级 |园豆:361 | 2011-06-30 17:19
非常感谢 !!
大漠孤烟。 | 园豆:12 (初学一级) | 2011-07-01 09:24
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册