sql - 在SQLite中使用SELECT…GROUP BY…HAVING

我正在自学SQL程序GalaXQL（基于SQLite）中进行练习17。我有三张桌子：

包含Stars的starid;
包含Planets和planetid的starid;
包含Moons和moonid的planetid。

我想返回与最大数量的行星和卫星相结合的starid。

我有一个查询，将返回starid，planetid和total行星+卫星。

如何更改此查询，使其仅返回与starid对应的单个max(total)，而不返回表？到目前为止，这是我的查询：

select
    stars.starid as sid,
    planets.planetid as pid,
    (count(moons.moonid)+count(planets.planetid)) as total
from stars, planets, moons
where planets.planetid=moons.planetid and stars.starid=planets.starid
group by stars.starid

最佳答案

让我们可视化一个可能由该数据库结构表示的系统，并查看我们是否无法将您的问题转换为有效的SQL。

我画了你一个银河系：

为了区分星星和行星与月亮，我将大写罗马数字用于starid值，并将小写罗马数字用于moonid值。而且由于每个人都知道天文学家在天文台的漫长夜晚中除了喝酒外没有其他事，因此我在您的planetid值中间加了一个无法解释的差距。当使用所谓的“代理” ID时，会出现这样的间隙，因为它们的值没有意义。它们只是行的唯一标识符。

如果您想遵循here's the galaxy naively loaded into SQL Fiddle（如果出现有关切换到WebSQL的弹出窗口，则可能需要单击“取消”并坚持使用SQL.js，此示例才能正常工作）。

让我们看看，您又想要什么？

我想返回与最大数量的行星和卫星相结合的starid

太棒了改写为，问题是：哪颗恒星与最多数量的绕行天体有关？

恒星（I）有1个行星，带有3个卫星；
恒星（II）有1个行星和1个月亮，以及1个行星和2个月亮；
恒星（III）有1个行星和1个月亮，以及2个行星没有卫星。

我们在这里所做的只是计算与每颗恒星相关的不同实体。总共有5个绕行轨道的恒星（II）是获胜者！因此，我们期望从一个有效的查询中得出的最终结果是：

| starid |
|--------|
| 2      |

我特意画出了这个令人敬畏的星系，以使“获胜”的恒星没有最多的行星，并且与拥有最多卫星的行星没有关联。如果这些天文学家不是一帆风顺，我可能还会从行星（1）上获得一个额外的月亮，这样我们的获胜星就不会与大多数卫星并列。如果星号（II）仅回答我们要问的问题，而不回答具有潜在相似查询的任何其他问题，那么对于我们来说很方便，以减少我们通过错误的查询获得正确答案的机会。

翻译迷失

我要做的第一件事是向您介绍显式的JOIN语法。这将是您的非常亲密的朋友。无论某些愚蠢的教程怎么说，您都将始终JOIN您的表。取而代之，请相信我的愚蠢建议（也可以选择阅读Explicit vs implicit SQL joins）。

显式的JOIN语法显示了我们如何要求表相互关联，并且保留WHERE子句的唯一目的是从结果集中过滤行。有a few different types，但是我们要从一个普通的INNER JOIN开始。从本质上讲，这就是原始查询执行的操作，它意味着您要在结果集中看到的所有数据都是所有三个表中都重叠的数据。查看原始查询的框架：

SELECT ... FROM stars, planets, moons
WHERE planets.planetid = moons.planetid
    AND planets.starid = stars.starid;

在这些条件下，与星无关的空间中某个孤立的行星发生什么变化（即其starid为NULL）？由于孤立的星球与stars表没有重叠，因此INNER JOIN不会将其包括在结果集中。

在SQL中，与NULL进行的任何相等或不相等比较都会得出NULL的结果，即使NULL = NULL也不正确！现在您的查询出现了问题，因为另一个条件是planets.planetid = moons.planetid。如果存在不存在相应卫星的行星，则该行星变为planets.planetid = NULL，并且该行星将不会出现在查询结果中。那不好！寂寞的行星必须算在内！

OUTER限制

幸运的是，有一个JOIN：OUTER JOIN，它将确保至少一个表始终显示在我们的结果集中。它们具有LEFT和RIGHT样式，以指示相对于JOIN关键字的位置哪个表受到特殊处理。 What joins does SQLite support?确认INNER和OUTER关键字是可选的，因此我们可以使用LEFT JOIN，请注意：

stars和planets通过共同的starid链接；
planets和moons通过共同的planetid链接；
stars和moons通过以上两个链接间接链接；
我们一直想统计所有的行星和所有的卫星。

SELECT
    *
FROM
    stars
        LEFT JOIN
    planets ON stars.starid = planets.starid
        LEFT JOIN
    moons ON planets.planetid = moons.planetid;

请注意，您现在没有一个大袋子表和一个WHERE子句，现在每个ON都有一个JOIN子句。当您发现自己使用更多表时，这将变得更容易阅读；并且由于这是标准语法，因此在SQL数据库之间相对可移植。

迷失在太空

我们的新查询基本上可以获取数据库中的所有内容。但这是否与我们银河系中的一切相对应？实际上，这里存在一些冗余，因为我们的两个ID字段（starid和planetid）存在于多个表中。这只是在实际使用案例中避免使用SELECT *全面语法的众多原因之一。我们只真正需要三个ID字段，而在此我们将投入另外两个技巧：

别名！您可以使用table_name AS alias语法为表指定更方便的名称。当您必须在一个多表查询中引用许多不同的列并且不想每次都键入完整的表名时，这会非常方便。
从starid表中获取planets并将stars完全排除在JOIN之外！拥有stars LEFT JOIN planets ON stars.starid = planets.starid意味着starid字段将是相同的，无论我们从哪张桌子得到它-只要恒星有任何行星。如果要计算星数，则需要此表，但要计算行星和卫星；根据定义，卫星绕行星运行，因此没有行星的恒星也没有卫星，可以忽略不计。（这是一个假设；请检查您的数据以确保它是合理的！也许您的天文学家比平时醉了！）

SELECT
    p.starid,    -- This could be S.starid, if we kept using `stars`
    p.planetid,
    m.moonid
FROM
    planets AS p
        LEFT JOIN
    moons AS m ON p.planetid = m.planetid;

结果：

| starid | planetid | moonid |
|--------|----------|--------|
|      1 |        1 |      1 |
|      1 |        1 |      2 |
|      1 |        1 |      3 |
|      2 |        2 |      6 |
|      2 |        3 |      4 |
|      2 |        3 |      5 |
|      3 |        7 |        |
|      3 |        8 |      7 |
|      3 |        9 |        |

数学！

现在，我们的任务是确定哪个明星是获胜者，为此，我们必须进行一些简单的计算。让我们先数月亮；由于他们没有“孩子”，每个孩子只有一个“父母”，因此很容易汇总：

SELECT
    p.starid,
    p.planetid,
    COUNT(m.moonid) AS moon_count
FROM
    planets AS p
        LEFT JOIN
    moons AS m ON p.planetid = m.planetid
GROUP BY p.starid, p.planetid;

结果：

| starid | planetid | moon_count |
|--------|----------|------------|
|      1 |        1 |          3 |
|      2 |        2 |          1 |
|      2 |        3 |          2 |
|      3 |        7 |          0 |
|      3 |        8 |          1 |
|      3 |        9 |          0 |

（注意：通常我们喜欢使用COUNT(*)，因为它很容易键入和阅读，但是在这里会给我们带来麻烦！因为我们两行的NULL，we have to use moonid to avoid counting moons that don't exist都有COUNT(moonid)值。）

到目前为止，一切都很好—我看到了六个行星，我们知道每个行星属于哪个恒星，并且每个行星都显示了正确的卫星数量。下一步，计算行星数。您可能会认为这需要一个子查询，以便还为每个行星添加moon_count列，但实际上比这要简单。如果我们GROUP BY恒星，我们的moon_count将从“每个星球的月球数量”转换为“每个星球的月球数量”，这很好：

SELECT
    p.starid,
    COUNT(p.planetid) AS planet_count,
    COUNT(m.moonid) AS moon_count
FROM
    planets AS p
        LEFT JOIN
    moons AS m ON p.planetid = m.planetid
GROUP BY p.starid;

结果：

| starid | planet_count | moon_count |
|--------|--------------|------------|
|      1 |            3 |          3 |
|      2 |            3 |          3 |
|      3 |            3 |          1 |

现在我们遇到了麻烦。 moon_count是正确的，但是您应该立即看到planet_count是错误的。为什么是这样？回顾未分组的查询结果，请注意有九行，每个starid三行，并且每一行的planetid值都为非空。这就是我们真正要问的是那里有多少个不同的行星时，我们才要求数据库对该查询进行计数。行星（1）与恒星（I）出现3次，但每次都是同一颗行星。解决方法是将DISTINCT关键字保留在COUNT()函数调用内。同时，我们可以将两列加在一起：

SELECT
    p.starid,
    COUNT(DISTINCT p.planetid)+ COUNT(m.moonid) AS total_bodies
FROM
    planets AS p
        LEFT JOIN
    moons AS m ON p.planetid = m.planetid
GROUP BY p.starid;

结果：

| starid | total_bodies |
|--------|--------------|
|      1 |            4 |
|      2 |            5 |
|      3 |            4 |

最终获胜者是...

计算图中每颗恒星周围的运行物体，我们可以看到total_bodies列是正确的。但是您并没有要求所有这些信息。您只想知道谁赢了。嗯，有很多方法可以到达那里，并且根据您的星系（数据库）的大小和组成，某些方法可能比其他方法更有效。一种方法是ORDER BY total_bodies表达式，使“获胜者”出现在顶部，LIMIT 1，这样我们就看不到失败者，而仅选择starid列（see it on SQL Fiddle）。

这种方法的问题在于它隐藏了联系。如果我们给银河系中的失星分别增加一个行星或卫星，该怎么办？现在，我们有了三路平局-每个人都是赢家！但是，当我们ORDER BY始终相同的值时，谁首先出现？在SQL标准中，这是未定义的。没有告诉谁会拔得头筹。您可能会对相同的数据运行两次相同的查询，并得到两个不同的结果！

因此，您可能更想问哪些恒星具有最多的轨道体，而不是在问题中指定仅知道一个值。这是一种更典型的基于集合的方法，在使用关系数据库时习惯于基于集合的思考并不是一个坏主意。在执行查询之前，您不知道结果集的大小。如果您要假设没有并列第一名，则必须以某种方式证明该假设的合理性。（由于天文学家经常发现新的月亮和行星，所以我很难证明这一点！）

我更喜欢编写此查询的方式是使用称为通用表表达式（CTE）的东西。 SQLite的最新版本和many other databases支持这些功能，但是最后我检查了GalaXQL是否使用了不包含此功能的SQLite引擎的较早版本。 CTE使您可以使用别名多次引用子查询，而不必每次都将其完整写出。使用CTE的解决方案如下所示：

WITH body_counts AS
    (SELECT
        p.starid,
        COUNT(DISTINCT p.planetid) + COUNT(m.moonid) AS total_bodies
    FROM
        planets AS p
            LEFT JOIN
        moons AS m ON p.planetid = m.planetid
    GROUP BY p.starid)
SELECT
    starid
FROM
    body_counts
WHERE
    total_bodies = (SELECT MAX(total_bodies) FROM body_counts);

结果：

| STARID |
|--------|
|      2 |

Check out this query in action on SQLFiddle。要确认此查询在出现平局的情况下可以显示多行，请尝试将最后一行的MAX()更改为MIN()。

只为你

在没有CTE的情况下执行此操作很难，但是如果表大小是可管理的，则可以执行此操作。在上面的查询中，我们的CTE别名为body_counts，并且在FROM子句和WHERE子句中两次引用它。我们可以用我们用来定义body_counts的语句替换这两个引用（在第二个子查询中一次不使用id列的情况下，将其删除）：

SELECT
    starid
FROM
    (SELECT
        p.starid,
        COUNT(DISTINCT p.planetid) + COUNT(m.moonid) AS total_bodies
    FROM
        planets AS p
            LEFT JOIN
        moons AS m ON p.planetid = m.planetid
    GROUP BY p.starid)
WHERE
    total_bodies = (SELECT MAX(total_bodies) FROM
        (SELECT
            COUNT(DISTINCT p.planetid)+ COUNT(m.moonid) AS total_bodies
        FROM
            planets AS p
                LEFT JOIN
            moons AS m ON p.planetid = m.planetid
        GROUP BY p.starid)
    );

这是GalaXQL中适合您的领带友好型方法。看到它正常工作。

既然您已经看到了两者，那么CTE版本难道不是很容易理解吗？ MySQL，here in SQLFiddle，将为didn't support CTEs until the 2018 release of version 8.0。幸运的是，SQLite不会，因为在这种情况下，添加到已经非常复杂的查询只是多余的琐事。

好吧，那很有趣-您是否很抱歉被问到？ ;）

（P.S.，如果您想知道第九号行星是怎么回事：巨型太空炸土豆片往往具有非常偏心的轨道。）

StarID

sql - 在SQLite中使用SELECT…GROUP BY…HAVING