原表:ID Name Score
1 Fish 10
2 Fish 8
3 Fish 13
4 Fish 6
5 Peter 78
6 Peter 86
7 Peter 80
8 Andy 32
9 Google 45
10 Google 42
目标:
ID Name Score
1 Fish 37
2 Peter 244
3 Andy 32
4 Google 87
麻烦大家帮我实现以下,就是统计出同一个NAME的总成绩。SQL的数据表也生成了。如:
DECLARE @Score TABLE([ID] int,[Name] varchar(20),[Score] float(8) )
INSERT @Score
SELECT '1','Fish','10' UNION ALL
SELECT '2','Fish','8' UNION ALL
SELECT '3','Fish','13' UNION ALL
SELECT '4','Fish','6' UNION ALL
SELECT '5','Peter','78' UNION ALL
SELECT '6','Peter','86' UNION ALL
SELECT '7','Peter','80' UNION ALL
SELECT '8','Andy','32' UNION ALL
SELECT '9','Google','45' UNION ALL
SELECT '10','Google','42'SELECT * FROM @Score
1 Fish 10
2 Fish 8
3 Fish 13
4 Fish 6
5 Peter 78
6 Peter 86
7 Peter 80
8 Andy 32
9 Google 45
10 Google 42
目标:
ID Name Score
1 Fish 37
2 Peter 244
3 Andy 32
4 Google 87
麻烦大家帮我实现以下,就是统计出同一个NAME的总成绩。SQL的数据表也生成了。如:
DECLARE @Score TABLE([ID] int,[Name] varchar(20),[Score] float(8) )
INSERT @Score
SELECT '1','Fish','10' UNION ALL
SELECT '2','Fish','8' UNION ALL
SELECT '3','Fish','13' UNION ALL
SELECT '4','Fish','6' UNION ALL
SELECT '5','Peter','78' UNION ALL
SELECT '6','Peter','86' UNION ALL
SELECT '7','Peter','80' UNION ALL
SELECT '8','Andy','32' UNION ALL
SELECT '9','Google','45' UNION ALL
SELECT '10','Google','42'SELECT * FROM @Score
?
-- Author: T.O.P
-- Create date: 2009/11/24
-- Version: SQL SERVER 2005
-- =============================================
declare @TB1 table([ID] int,[Name] varchar(6),[Score] int)
insert @TB1
select 1,'Fish',10 union all
select 2,'Fish',8 union all
select 3,'Fish',13 union all
select 4,'Fish',6 union all
select 5,'Peter',78 union all
select 6,'Peter',86 union all
select 7,'Peter',80 union all
select 8,'Andy',32 union all
select 9,'Google',45 union all
select 10,'Google',42select [Name], sum([Score]) as [Score]
from @TB1
group by [Name]
--测试结果:
/*
Name Score
------ -----------
Andy 32
Fish 37
Google 87
Peter 244(4 row(s) affected)*/
是想的简单了还是??
-- =============================================
-- Author: T.O.P
-- Create date: 2009/11/24
-- Version: SQL SERVER 2005
-- =============================================
declare @TB1 table([ID] int,[Name] varchar(6),[Score] int)
insert @TB1
select 1,'Fish',10 union all
select 2,'Fish',8 union all
select 3,'Fish',13 union all
select 4,'Fish',6 union all
select 5,'Peter',78 union all
select 6,'Peter',86 union all
select 7,'Peter',80 union all
select 8,'Andy',32 union all
select 9,'Google',45 union all
select 10,'Google',42select row_number() over (order by getdate()) as ID,[Name], sum([Score]) as [Score]
from @TB1
group by [Name]
--测试结果:
/*
ID Name Score
-------------------- ------ -----------
1 Andy 32
2 Fish 37
3 Google 87
4 Peter 244(4 row(s) affected)*/
INSERT @Score
SELECT '1','Fish','10' UNION ALL
SELECT '2','Fish','8' UNION ALL
SELECT '3','Fish','13' UNION ALL
SELECT '4','Fish','6' UNION ALL
SELECT '5','Peter','78' UNION ALL
SELECT '6','Peter','86' UNION ALL
SELECT '7','Peter','80' UNION ALL
SELECT '8','Andy','32' UNION ALL
SELECT '9','Google','45' UNION ALL
SELECT '10','Google','42'
select ID=(select count(*) from (select [name],sum([Score])[Score] from @Score group by name)a where a.[Score]<=t.[Score]),
[Name],
[Score]
from (select [name],sum([Score])[Score] from @Score group by name) t
/*
ID Name Score
----------- -------------------- ----------------------
1 Andy 32
2 Fish 37
3 Google 87
4 Peter 244
*/
id=(select count(distinct name) from @score where name<=t.name),
name,
sum(score) as score
FROM @Score t
group by name
/**
id name score
----------- -------------------- -----------------------------------------------------
1 Andy 32.0
2 Fish 37.0
3 Google 87.0
4 Peter 244.0(所影响的行数为 4 行)
**/
from
(
select *,grp=case when id!=(select min(id) from @score where name=t.name) then (select min(id) from @score where name=t.name)
else id end
from @score t
)K
group by name,grp
order by grpFish 37
Peter 244
Andy 32
Google 87
INSERT @Score
SELECT '1','Fish','10' UNION ALL
SELECT '2','Fish','8' UNION ALL
SELECT '3','Fish','13' UNION ALL
SELECT '4','Fish','6' UNION ALL
SELECT '5','Peter','78' UNION ALL
SELECT '6','Peter','86' UNION ALL
SELECT '7','Peter','80' UNION ALL
SELECT '8','Andy','32' UNION ALL
SELECT '9','Google','45' UNION ALL
SELECT '10','Google','42'SELECT * FROM @Scoreselect id=row_number()over (order by getdate()),name,sum(score)
from
(
select *,grp=case when id!=(select min(id) from @score where name=t.name) then (select min(id) from @score where name=t.name)
else id end
from @score t
)K
group by name,grp
order by grp1 Fish 37
2 Peter 244
3 Andy 32
4 Google 87