SQL帮助查找最大总价值
我需要帮助找到在此基础上表中的球员和球队名称的总点数:SQL帮助查找最大总价值
Tbl.STATS [Match_ID,Player_ID,Points_Scored,Team_Name]
任何建议或帮助,将不胜感激!
上所有的球队
select player_id, team_name, sum(points_scored) sum_score
from stats
group by player_id, team_name
order by sum_score desc
球员和球队的名字所有玩家(例如,鲍勃打了老鹰,所以不考虑当Bob打了鲨鱼)
select sum(points_scored) sum_score
from stats
where player_id = 1 and team_name = 'Eagles'
但问题问还有一件事,所以要回答这个问题,MAX总分:
select max(sum_score) MaxTotalScore
from
(
select player_id, team_name, sum(points_scored) sum_score
from stats
group by player_id, team_name
) X
SELECT play er_id,TEAM_NAME,SUM(points_scored)FROM STATS集团通过player_id,TEAM_NAME
“我希望找到的球员说 得分最多的总分和什么 队球员上。”
我通常不会涉足Oracle标记,但我相信这应该工作!
WITH T AS
(
SELECT Player_ID,
Team_Name,
SUM(Points_Scored) OVER (PARTITION BY Player_ID) AS Points_Total
FROM Tbl.STATS
), T2 AS
(
SELECT Player_ID,
Team_Name,
Points_Total,
RANK() OVER (ORDER BY Points_Total DESC) AS RN
FROM T
)
SELECT Player_ID,
Team_Name,
Points_Total
FROM T2
WHERE RN=1
谢谢!我也有SQL Server,我也试图找出它。对不起,我很痛苦。 – Potter 2011-03-21 00:03:35
@Potter - 我的答案应该适用于SQL Server 2005+ – 2011-03-21 00:04:51
玩家可以参加多支球队(转会)吗?你是什么意思“MAX总数”? 'MAX'和'SUM'是两码事。你还使用了什么RDBMS? – 2011-03-20 23:07:38
玩家可以通过TRANSFERS实体为多支球队打球,但在这种情况下并不需要。这将是目前统计转会后的季节。我正在使用ORACLE 2007. – Potter 2011-03-20 23:19:20
那么你是否在寻找总分最高的单人/团队组合? – 2011-03-20 23:22:24