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要求）的时刻。一旦我想通的建立第一部分我会尽力总

完全奇怪以什么方式？ – gkalpak

嗯，对我来说已经很晚了，但是可以在'GROUP BY q1.country'之前添加'WHERE q1.country = q2.country AND q1.country = q3.country'。它会更好吗？ – gkalpak

它似乎没有以独立的方式计算计数。仍然显示了一些计数，但他们是错误的（就像美国队在某场比赛中赢得了2200枚金牌）。我的数据库中的数据不是问题 –

答

的数据是没有问题的 - 你有一个隐含的笛卡尔每个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。

SQL：从相同的表/字段计数，但数值不同

相关推荐