sql 怎么些

[ybblack001]

一个表
name source
张三 1
张三 2
李四 3
李四 2
王五 1

-----------
需要得出

name 分数 总分数

张三 3 9
李四 5 9
王五 1 9

求教

[shulin85]

SELECT SUM[source] FROM [TABLE]

[tomsoncat]

select name,sum(source) from table groupby name

[ybblack001]

没你说那么简单吧...首先要group 然后聚合,但是第三个字段需要group前的聚合结果...

[ybblack001]

关键是总分数怎么求

[tomsoncat]

没看到最后一列
select name,sum(source)，（select sum(source) from table） from table groupby name

[randomfeel]

select name,a1,
		a2=(select sum(source) from tablename) 
 from
(select name,sum(source) as a1 from tablename group by name) t 

[porschev]

select [name] as 用户名,SUM(source) as 分数,(select SUM(source) from 表名) as 总分数 from 表名 group by [name]

[randomfeel]

e,兜了个圈
不用这样写的
#6那样就不错

[ybblack001]

饿...只能用子表么....实际中这个子表可是有20行....

[scudflying]

SELECT [name],(SELECT sum([source]) FROM [Table_1] where [name]=a.[name]) as '分数',(SELECT SUM([source]) FROM [Table_1]) as '总分'
  FROM [Table_1] as a
group by [name]

[s370092561]

[Quote=引用 8 楼 porschev 的回复:]
SQL code


select [name] as 用户名,SUM(source) as 分数,(select SUM(source) from 表名) as 总分数 from 表名 group by [name]
[/Quote]

顶！

[ybblack001]

唉...为什么不能直接对聚合出来的字段再次聚合呢....郁闷,,,子句好长的,以后维护是个问题...

[porschev]

[Quote=引用 13 楼 ybblack001 的回复:]
唉...为什么不能直接对聚合出来的字段再次聚合呢....郁闷,,,子句好长的,以后维护是个问题...
[/Quote]

这个。。。

[cpp2017]

用多个sql

delcare @TotalScore int
select @TotalScore = sum(source) from T


select name,sum(分数) as 分数，@totalScore as 总分数
from t group by name,@totalScore

[jinguanghai]

一个人的分数 select sum(分数) from 表名 group by name
总分 select sum(分数) from 表名

[wwfgu00ing]

[Quote=引用 11 楼 scudflying 的回复:]
SQL code
SELECT [name],(SELECT sum([source]) FROM [Table_1] where [name]=a.[name]) as '分数',(SELECT SUM([source]) FROM [Table_1]) as '总分'
FROM [Table_1] as a
group by [name]
[/Quote]
正解

[c慧子]

写个方法吖。
fucntion f_value(varchar @name)
as
begin
declare @a int
select @a = sum(分数) from 表 where name= @name
reuturns (@a)
end

[jxmlearner]

[Quote=引用 7 楼 randomfeel 的回复:]
SQL code
select name,a1,
a2=(select sum(source) from tablename)
from
(select name,sum(source) as a1 from tablename group by name) t
[/Quote]
正解！

[czhhlove]

[Quote=引用 7 楼 randomfeel 的回复:]
SQL code
select name,a1,
a2=(select sum(source) from tablename)
from
(select name,sum(source) as a1 from tablename group by name) t
[/Quote]
只需将a1换成分数 a2换成总分数 就行啦

[pigHead_chen]

[Quote=引用 6 楼 tomsoncat 的回复:]
没看到最后一列
select name,sum(source)，（select sum(source) from table） from table groupby name
[/Quote]
+