三张表结构如下:
表名一 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比如:取出表二李四的总分,和取出表三李四的总分 ,加在一起的总分的语句是什么?(也就是二个表有许多人员每个表都是一个计算总分的,最后合成一个总分)
表名一 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
?
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 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
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
(
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