这个问题过go 几周一直困扰着我,但到目前为止,我达成的解决方案似乎还不够好,不足以消除我的疑虑.
Problem Statement:构建一个表(查询),其中显示以下列(球队名称、比赛次数、胜利、失败、平局和得分).每支球队的得分都是在知道每次胜利得3分、平局得1分的情况下计算出来的,而失败则完全没有影响.
Table Schemas:个
表"teams":
Column name | Type |
---|---|
id | int |
name | varchar(50) |
表"matches":
Column name | Type |
---|---|
id | int |
team_1 | int |
team_2 | int |
team_1_goals | int |
team_2_goals | int |
Sample Data:个
表"teams":
id | name |
---|---|
1 | CEARA |
2 | FORTALEZA |
3 | GUARANY DE SOBRAL |
4 | FLORESTA |
表"matches":
id | team_1 | team_2 | team_1_goals | team_2_goals |
---|---|---|---|---|
1 | 4 | 1 | 0 | 4 |
2 | 3 | 2 | 0 | 1 |
3 | 1 | 3 | 3 | 0 |
4 | 3 | 4 | 0 | 1 |
5 | 1 | 2 | 0 | 0 |
6 | 2 | 4 | 2 | 1 |
Expected Output:
name | matches | victories | defeats | draws | score |
---|---|---|---|---|---|
CEARA | 3 | 2 | 0 | 1 | 7 |
FORTALEZA | 3 | 2 | 0 | 1 | 7 |
FLORESTA | 3 | 1 | 2 | 0 | 3 |
GUARANY DE SOBRAL | 3 | 0 | 3 | 0 | 0 |
What I've managed to do so far:
SELECT
t.name,
count(m.team_1) filter(WHERE t.id = m.team_1)
+ count(m.team_2) filter(WHERE t.id = m.team_2) "matches",
count(m.team_1) filter(WHERE t.id = m.team_1 AND m.team_1_goals > m.team_2_goals)
+ count(m.team_2) filter(WHERE t.id = m.team_2 AND m.team_1_goals < m.team_2_goals) "victories",
count(m.team_1) filter(WHERE t.id = m.team_1 AND m.team_1_goals < m.team_2_goals)
+ count(m.team_2) filter(WHERE t.id = m.team_2 AND m.team_1_goals > m.team_2_goals) "defeats",
count(m.team_1) filter(WHERE t.id = m.team_1 AND m.team_1_goals = m.team_2_goals)
+ count(m.team_2) filter(WHERE t.id = m.team_2 AND m.team_1_goals = m.team_2_goals) "draws",
((count(m.team_1) filter(WHERE t.id = m.team_1 AND m.team_1_goals > m.team_2_goals)
+ count(m.team_2) filter(WHERE t.id = m.team_2 AND m.team_1_goals < m.team_2_goals))* 3) +
count(m.team_1) filter(WHERE t.id = m.team_1 AND m.team_1_goals = m.team_2_goals)
+ count(m.team_2) filter(WHERE t.id = m.team_2 AND m.team_1_goals = m.team_2_goals) "score"
FROM
teams t
JOIN matches m ON t.id IN (m.team_1, m.team_2)
GROUP BY t.name
ORDER BY "victories" DESC
其理论上输出正确答案.
我试着用像CASE WHEN
或更大的JOIN
这样的花哨的东西来制作,但没有取得好的效果.我想知道的是,就编写和服务器性能而言,是否有更好的方法来执行此查询.
感谢任何人的帮助!