优化体育网站排名查询
优化体育网站排名查询
提问于 2012-01-17 22:06:31
我继承了一个跟踪曲棍球联盟比赛结果的应用程序。结果表如下所示：
[dbo].[league_division_games](
[id] [int] IDENTITY(1,1) NOT NULL,
[division] [int] NULL,
[team1] [int] NULL,
[team1_score] [int] NULL,
[team2] [int] NULL,
[team2_score] [int] NULL,
[shootout] [bit] NOT NULL)现有的查询是一个令人讨厌的游标，执行起来非常糟糕。我甚至不会把它贴在这里！
我开始重写这篇文章，但对我来说，它看起来相当复杂。
SELECT
teams.id
,teams.name
,IsNull(COUNT(games.id), 0) AS GP
,SUM(CASE WHEN (teams.id = games.team1 AND games.team1_score > games.team2_score) OR (teams.id = games.team2 AND games.team2_score > games.team1_score) THEN 1 ELSE 0 END) AS W
,SUM(CASE WHEN (teams.id = games.team1 AND games.team1_score < games.team2_score) OR (teams.id = games.team2 AND games.team2_score < games.team1_score) THEN 1 ELSE 0 END) AS L
,SUM(CASE WHEN (teams.id = games.team1 AND games.team1_score = games.team2_score) OR (teams.id = games.team2 AND games.team1_score = games.team2_score) THEN 1 ELSE 0 END) AS T
FROM
dbo.league_division_teams teams
LEFT OUTER JOIN dbo.league_division_games games ON teams.id = games.team1 OR teams.id = games.team2
WHERE
teams.division = @DIV
GROUP BY
teams.id,
teams.name基本上，我必须不断检查比分的每一方，看胜负，平局，进球，进球。
一点复杂性-在曲棍球中，如果是加时赛，输家算1分，胜方算2分。另外，如果是点球大战，那么得分等于两个得分中最低的一个。
由于这些原因，聚合case语句将变得非常大，并且可能不会有很好的性能。
我已经考虑过创建一个CTE来让这个问题变得平坦一点，但还没有走上这条路。
有没有人用不同的方式来处理这个问题？我想这类统计数据是很常见的。
谢谢。
回答 1
Stack Overflow用户
发布于 2012-01-17 22:28:38
另一种方法是分别聚合两个“边”，并在最后将它们组合在一起。你必须尝试一下，看看它是否表现得更好，但我认为是这样的：
select
id, name, sum(gp), sum(w), sum(l), sum(t)
from (
SELECT
teams.id
,teams.name
,IsNull(COUNT(games.id), 0) AS GP
,CASE WHEN team1_score > team2_score THEN 1 ELSE 0 END AS W
,CASE WHEN team1_score < team2_score THEN 1 ELSE 0 END AS L
,CASE WHEN team1_score = team2_score THEN 1 ELSE 0 END AS T
FROM
dbo.league_division_teams teams
LEFT OUTER JOIN dbo.league_division_games games ON teams.id = games.team1
union
SELECT
teams.id
,teams.name
,IsNull(COUNT(games.id), 0) AS GP
,CASE WHEN team2_score > team1_score THEN 1 ELSE 0 END AS W
,CASE WHEN team2_score < team1_score THEN 1 ELSE 0 END AS L
,CASE WHEN team2_score = team1_score THEN 1 ELSE 0 END AS T
FROM
dbo.league_division_teams teams
LEFT OUTER JOIN dbo.league_division_games games ON teams.id = games.team2
)
group by
id, name页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/8895959
复制相关文章
相似问题
腾讯云开发者
