求sql语句，总分排名!

[tian1984528]

三张表结构如下:
表名一 user:
字段
id,name
1，张三
2，李四
3, 王五


表名二fenlist:

字段
id, user_id, fenshuo
1, 王五的ID, 20
2， 李四的ID, 5
3, 李四的ID, 6

表三分数三fenlist_s

字段
id, u_id, fenshuo
1, 王五的ID, 30
2, 王五的ID, 20
3, 王五的ID, 30
4, 李四的ID, 2
5, 李四的ID, 6

比如：取出表二李四的总分，和取出表三李四的总分 ，加在一起的总分的语句是什么？（也就是二个表有许多人员每个表都是一个计算总分的，最后合成一个总分）

[修改一下昵称]

select UserName,sum(results) results  from
(
    select '张三' as UserName,30 as results
	union all
	select '张三' as UserName,40 as results
) tab group by tab.UserName
?

[tian1984528]

不单是张三，其他人员人总分也要取

[永生天地]

select a.name,sum(b.fenshuo) 总分 
from [user] a left join 
(select * from (
select [user_id],fenshuo from fenlist union all
select u_id,fenshuo from fenlist_s ) t) b 
on a.id=b.[user_id]
group by a.name

[仙道彰]

select sum(fenshuo) as 李四的分数  from
(
select fenshuo=sum(fenshuo) from user,fenlist where id=user_id and name='李四'
union all
select fenshuo=sum(fenshuo) from user,fenlist_s where id=user_id and name='李四'
) as  t

[--小F--]

select
   a.name,isnull(b.fenshuo,0)+isnull(c.fenshuo,0) as fenshuo 
from
   user a,
   (select user_id,sum(fenshuo) as fenshuo from fenlist group by user_id)b,
   (select user_id,sum(fenshuo) as fenshuo from fenlist_sgroup by user_id)c
where
   a.id=b.user_id
and
   a.id=c.user_id

   

[仙道彰]

[Quote=引用 3 楼 tian1984528 的回复:]

不单是张三，其他人员人总分也要取
[/Quote]

select sum(fenshuo),name from
( 
select fenshuo=sum(fenshuo),name from user,fenlist where id=user_id  group by name
 union all 
select fenshuo=sum(fenshuo),name from user,fenlist_s where id=user_id group by name
 ) as t
group by name

[tian1984528]

[Quote=引用 2 楼 xys_777 的回复:]
SQL code

select a.name,sum(b.fenshuo) 总分
from [user] a left join
(select * from (
select [user_id],fenshuo from fenlist union all
select u_id,fenshuo from fenlist_s ) t) b
on a.id=b.[user_i……
[/Quote]

这个语句可以的，但是没有按最高分排序

[tian1984528]

谢谢你们！

[wanlinghai]

[Quote=引用 8 楼 tian1984528 的回复:]
谢谢你们！
[/Quote]
哈哈·