SQL:从相同的表/字段计数,但数值不同
在我的数据库项目中,我必须建立一个关于奥运会数据的数据库。SQL:从相同的表/字段计数,但数值不同
我必须建立以下查询,以及:
为用户提供特定的奥运会奖牌计算表。奖牌表应包含 国家的国际奥委会代码,其次是金牌,银牌,铜牌和总奖牌的数量。首先应该是 ,按黄金数量排序,然后是银色,最后是青铜色。
基本上,我有一个表Medals
,其中包含一些参加者在某些奥运比赛中获得的奖牌。
奖牌以下列方式储存:我的牌桌Medals
的color
场中的“金牌”,“银牌”,“铜牌”。
我尝试使用下面的查询:
SELECT q1.country, q1.name as "Game", q1.cntG, q2.cntS, q3.cntB FROM
(
SELECT c.countryName as country, g.name as name, count(m.idMedal) as cntG
FROM Game g
INNER JOIN Participant p
ON p.fkGame = g.idGame
INNER JOIN Country c
ON p.fkCountry = c.idCountry
INNER JOIN Medals m
ON m.fkMedalist = p.idParticipant
WHERE
g.name = "2012 Summer Olympics"
AND m.color like '%Gold%'
GROUP BY c.countryName
ORDER BY c.countryName, cntG DESC
) as q1,
(
SELECT c.countryName as country, g.name as name, count(m.idMedal) as cntS
FROM Game g
INNER JOIN Participant p
ON p.fkGame = g.idGame
INNER JOIN Country c
ON p.fkCountry = c.idCountry
INNER JOIN Medals m
ON m.fkMedalist = p.idParticipant
WHERE g.name = "2012 Summer Olympics"
AND m.color like '%Silver%'
GROUP BY c.countryName
ORDER BY c.countryName, cntS DESC
) as q2,
(
SELECT c.countryName as country, g.name as name, count(m.idMedal) as cntB
FROM Game g
INNER JOIN Participant p
ON p.fkGame = g.idGame
INNER JOIN Country c
ON p.fkCountry = c.idCountry
INNER JOIN Medals m
ON m.fkMedalist = p.idParticipant
WHERE g.name = "2012 Summer Olympics"
AND m.color like '%Bronze%'
GROUP BY c.countryName
ORDER BY c.countryName, cntB DESC
) as q3
GROUP BY q1.country
ORDER BY q1.cntG, q2.cntS, q3.cntB DESC
那么,它给了我完全怪异的结果。我知道这个查询有问题,但无法弄清楚它是什么!
希望你能帮助我:)
感谢
注:我忽略了查询的总数量(如assignement要求)的时刻。一旦我想通的建立第一部分我会尽力总
的数据是没有问题的 - 你有一个隐含的笛卡尔每个Q1,Q2和Q3之间加入一个事实是一个问题。尝试:
SELECT c.countryName as country,
count(case m.idMedal when 'Gold medal' then 1 end) as cntG,
count(case m.idMedal when 'Silver medal' then 1 end) as cntS,
count(case m.idMedal when 'Bronze medal' then 1 end) as cntB
FROM Game g
INNER JOIN Participant p ON p.fkGame = g.idGame
INNER JOIN Country c ON p.fkCountry = c.idCountry
INNER JOIN Medals m ON m.fkMedalist = p.idParticipant
WHERE g.name = "2012 Summer Olympics"
GROUP BY c.countryName
ORDER BY cntG DESC, cntS DESC, cntB DESC
我是SUM()技巧的忠实粉丝,因为它不那么冗长。 –
哇,我忘了SUM技巧,工作正常以及:) thx –
对不起,您的查询是一个真正的混乱。你不能简单地以这种方式加入3个子查询。
但是,你可以有你在一个查询想要的东西。我给你的伪代码,并留下你的细节:)
SELECT
[countryName],
SUM(color like '%Gold%') as total_gold,
SUM(color like '%Silver%') as total_silver,
SUM(color like '%Bronze%') as total_bronze,
COUNT(*) as total
FROM Medals
INNER JOIN Participant (...)
INNER JOIN Country (...)
INNER JOIN Game (...)
WHERE (...)
GROUP BY [countryName]
ORDER BY total_gold DESC, total_silver DESC, total_bronze DESC;
有很多方法可以解决,甚至提高你的查询,其中之一是增加
...
WHERE q1.country = q2.country
AND q1.country = q3.country
(只是GROUP BY q1.country
之前)。
另一解决方案是对JOIN Q1,Q2和Q3 ON country
。
完全奇怪以什么方式? – gkalpak
嗯,对我来说已经很晚了,但是可以在'GROUP BY q1.country'之前添加'WHERE q1.country = q2.country AND q1.country = q3.country'。它会更好吗? – gkalpak
它似乎没有以独立的方式计算计数。仍然显示了一些计数,但他们是错误的(就像美国队在某场比赛中赢得了2200枚金牌)。我的数据库中的数据不是问题 –