php - SELECT SUM - SQL 查询不工作

Question

我创建了一个基于奥运会的小型数据库，用于运行一些 sql 查询。我正在尝试生成一个表格，显示每个国家/地区的名称以及根据来自这些国家/地区的运动员赢得的金牌、银牌和铜牌总数。这是我的关系模型 View 的屏幕截图:

Relational Model View

这是我尝试运行的查询:

<h1>Olympics Database</h1>
<h3>Summary Information</h3>

    <h4>Number of Olympic Athletes from United Kingdom</h4>

<?php

    $conn = mysqli_connect('localhost', '#####', '#####') or die ('Could not connect:' . mysqli_error($conn));
    echo 'Successfully Connected. <br/>';

    mysqli_select_db($conn, '#####') or die('Database will not open');
    echo 'Database Connected. <br/> <br/>';

    $query2 = 'SELECT CountryAbbrev, (SELECT COUNT(CountryAbbrev) FROM athlete WHERE MedalID = 401 AND ath.CountryAbbrev = athlete.CountryAbbrev) AS Gold, (SELECT COUNT(CountryAbbrev) FROM athlete WHERE MedalID = 402 AND ath.CountryAbbrev = athlete.CountryAbbrev) AS Silver, (SELECT COUNT(CountryAbbrev) FROM athlete WHERE MedalID = 403 AND ath.CountryAbbrev = athlete.CountryAbbrev) AS Bronze FROM athlete AS ath';
    $result2 = mysqli_query($conn, $query2) or die ('Invalid Query');

    echo'<table><tr><th>Country Name</th><th>Gold</th><th>Silver</th><th>Bronze</th></tr>';
    $row = mysqli_fetch_row($result2);
    echo'<tr><td>' .$row[0].'</td><td>' .$row[1].'</td><td>' .$row[2].'</td><td>' .$row[3].'</td></tr>';
    echo'</table>';

    mysqli_close($conn);
    ?>

我得到的只是无效查询。根据我的关系模型 View ，谁能帮我弄清楚如何在一张表中显示每个国家/地区获得的金牌、银牌和铜牌总数？

更新:这是输出的屏幕截图:

Answer 1

由于您在表中选择未定义的字段，您的原始 SQL 查询返回无效查询。即 athlete 表没有名为 MedalName 的字段。

您可以使用以下 SQL 查询，通过了解每种奖牌类型的 ID，我假设金牌、银牌和铜牌分别为 1,2,3。

SELECT CountryAbbrev,
 (SELECT COUNT(CountryAbbrev) FROM athlete WHERE MedalID = 1 AND ath.CountryAbbrev = athlete.CountryAbbrev) AS Gold, 
(SELECT COUNT(CountryAbbrev) FROM athlete WHERE MedalID = 2 AND ath.CountryAbbrev = athlete.CountryAbbrev) AS Silver, 
(SELECT COUNT(CountryAbbrev) FROM athlete WHERE MedalID = 3 AND ath.CountryAbbrev = athlete.CountryAbbrev) AS Bronze 
FROM athlete AS ath

更新

抱歉，应该是 COUNT 而不是 SUM。查看 SUM 和 COUNT 之间的区别 from this link