使用SELECT … GROUP BY …在SQLite中使用

>包含starid的星星;
>包含planetid和starid的行星;
>包含月亮和平面的卫星.

我想归还与最大数量的行星和卫星相结合的恒星.

我有一个查询将返回starid,planetid和total行星卫星.

如何更改此查询以使其仅返回与max(总计)相对应的单个starid,而不是表格？这是我目前的查询：

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

解决方法

我画了一个星系：

为了区分恒星和行星与卫星,我使用大写罗马数字作为星号值,使用小写罗马数字作为月球值.因为每个人都知道天文学家在天文台的那些漫长的夜晚与饮酒无关,所以我在你的星际价值中间留下了一个无法解释的空隙.当使用所谓的“代理”ID时,会发生这样的差距,因为它们的值没有意义;它们只是行的唯一标识符.

如果你想跟随,here’s the galaxy naively loaded into SQL Fiddle(如果你得到一个关于切换到WebSQL的弹出窗口,你可能需要点击“取消”并坚持使用SQL.js这个例子才能工作).

让我们看看,你又想要什么？

I want to return the starid associated with the greatest number of planets and moons combined

真棒.重新说明,问题是：哪个恒星与最大数量的轨道物体有关？

>星(I)有1个行星,有3个卫星;
>星(II)有1个行星,1个月球,1个行星,2个月球;
>星(III)有1个行星,1个月球和2个没有卫星的行星.

我们在这里所做的只是计算与每颗恒星相关的不同实体.共有5个轨道物体,星(II)是胜利者！因此,我们对工作查询的最终结果是：

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

我故意画出这个令人敬畏的星系,使得“获胜”的恒星没有最多的行星,并且与拥有最多卫星的行星无关.如果那些天文学家不是全部三张风,我可能还会从行星(1)中获得额外的月亮,这样我们的获胜恒星就不会被大部分卫星所束缚.如果星(II)只回答我们提出的问题而不是任何其他可能类似查询的问题,那么在本演示中对我们来说会很方便,以减少通过错误查询得出正确答案的机会.

迷失在翻译中

我想要做的第一件事是向您介绍显式的JOIN语法.这将是你非常亲密的朋友.无论一些愚蠢的教程说什么,你总是会加入你的表.相信我的愚蠢建议(可选择阅读Explicit vs implicit SQL joins).

显式JOIN语法显示了我们如何要求表彼此关联并保留WHERE子句的唯一目的是从结果集中过滤行.有a few different types,但我们要开始的是一个简单的老INNER JOIN.这基本上是您的原始查询执行的内容,它意味着您希望在结果集中看到的所有内容都是在所有三个表中重叠的数据.查看原始查询的框架：

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

鉴于这些条件,在空间中某个与星形无关的孤立行星(即它的星形为NULL)会发生什么？由于孤立的行星与星表没有重叠,因此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,注意：

>恒星和行星由一个共同的恒星联系在一起;
>行星和卫星由一个共同的平面相连;
>星和月亮由上述两个环节间接联系起来;
>我们总是想要计算所有的行星和所有的卫星.

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

请注意,现在每个JOIN都有一个ON子句,而不是有一个大包或表和WHERE子句.当您发现自己使用更多表格时,这将更容易阅读;并且因为这是标准语法,所以它在SQL数据库之间相对可移植.

迷失在太空

我们的新查询基本上抓住了数据库中的所有内容.但这是否与我们银河系中的所有东西相对应？实际上,这里有一些冗余,因为我们的两个ID字段(starid和planetid)存在于多个表中.这只是在实际用例中避免SELECT * catch-all语法的众多原因之一.我们只需要三个ID字段,而且在我们使用它时我会再投入两个技巧：

>别名！您可以使用table_name AS别名语法为表提供更方便的名称.当您必须在多表查询中引用许多不同的列并且您不希望每次都输入完整的表名时,这可能非常方便.
>从行星表中抢夺星际,并将星星完全留在JOIN之外！拥有星星LEFT JOIN行星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(*),因为它很容易输入和读取,但它会让我们在这里遇到麻烦！因为我们的两个行对于moonid有一个NULL值,we have to use COUNT(moonid) to avoid counting moons that don’t exist.)

到目前为止,这么好 – 我看到六个行星,我们知道每个属于哪个恒星,并为每个行星显示正确数量的卫星.下一步,计算行星.您可能认为这需要一个子查询,以便为每个行星添加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都有一个非null值.这就是我们要求数据库用这个查询来计算的,当我们真正要问的是有多少不同的行星？星球(1)出现三次星(I),但每次都是同一个星球.修复是将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列是正确的.但你没有要求所有这些信息;你只想知道谁赢了.嗯,有很多方法可以实现,并且根据银河系(数据库)的大小和构成,有些方法可能比其他方法更有效.一种方法是对total_bodies表达式进行ORDER BY,以便“赢家”出现在顶部,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中适用于您.看它工作here in SQLFiddle.

既然你已经看过两者,那CTE版本是不是更容易理解？ MySQL,didn’t support CTEs until the 2018 release of version 8.0,additionally demand aliases for our subqueries.幸运的是,SQLite没有,因为在这种情况下,它只是添加到已经过于复杂的查询的额外措辞.

嗯,这很有趣 – 你有没有问过你？

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!