MySQL:为什么每个组查询的最大 N 个成员有效? [英] MySQL: why this max N members per group query works?

问题描述

我是 MySQL 的初学者.我试图编写一个查询来查找每个组的最大 N 成员.以下代码完成了这项工作，其中本例是获取每个国家的前 2 名 ID 最大的人:

I'm a beginner in MySQL. I tried to write a query for finding the max N members per group. The following code does the job, which in this case is to get the top 2 persons per country with greatest id:

SELECT co.id, co.person, co.country
FROM person co
WHERE (
SELECT COUNT(*)
FROM person ci
WHERE  co.country = ci.country      -- controlling grouping column
AND co.id < ci.id                       -- controlling min or max 
) < 2                                           -- controlling number of return per group
;

但是，我无法理解为什么会这样.有人可以向我解释一下吗?如果你能告诉我那就太好了:

However, I was unable to understand why this is working. Could someone please explain to me? It would be great if you can tell me:

1. 内部SELECT COUNT(*)返回的结果是什么?不就是一个数字吗?
2. 为什么要使用这个特定的内部 WHERE ... AND ... 子句?
3. 为什么外层 WHERE() <2控制每组的返回次数?

1. what is the result returned by the inner SELECT COUNT(*)? Isn't it just a single number?
2. why use this particular inner WHERE ... AND ... clause?
3. why the outer WHERE () < 2 controls the number of return per group?

非常感谢您的帮助.

DDL

CREATE TABLE `person` (
    `id` INT(11) NULL DEFAULT NULL,
    `country` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin',
    `person` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin'
)
COLLATE='utf8_bin'
ENGINE=InnoDB
;

INSERT INTO `person` (`id`, `country`, `person`) VALUES
 (1, 'Austria', 'Sue'),
(2, 'Austria', 'Anie'),
(3, 'Australia', 'John'),
(4, 'Australia', 'Brian'),
(5, 'UK', 'Jim'),
(6, 'UK', 'Tim'),
(7, 'USA', 'David'),
(8, 'USA', 'Mike'),
(9, 'USA', 'Tom'),
(10, 'N. Korea', 'Joe'),
(11, 'N. Korea', 'Hue'),
(12, 'N. Korea', 'Rick'),
(13, 'N. Korea', 'Jamy'),
(14, 'Finland', 'Kimi');

推荐答案

相关子查询返回具有相同国家和更高 id 的人数.因此，如果将其与 < 进行比较2 你只会为每个国家选择两个最高的 id.您可以通过查看选择子查询的结果而不是将其用作约束来更好地了解这一点:

The correlated subquery returns the number of persons with the same country and a higher id. So if you compare that with < 2 you will only select the two highest ids for each country. You may get a better idea of this by looking at the results of selecting the subquery instead of using it as a constraint:

SELECT co.id, co.person, co.country,
    (
        SELECT COUNT(*)
        FROM person ci
        WHERE  co.country = ci.country      -- controlling grouping column
        AND co.id < ci.id                       -- controlling min or max 
    ) AS higher_ids
FROM person co

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9c3cfe469dd299b3160d09e97e73e499

请注意，除非有专门针对此模式的优化(我非常怀疑)，否则此查询将是 O(N^2).

Note that unless there are optimizations that specifically are looking for this pattern, which I highly doubt, this query is going to be O(N^2).

这篇关于MySQL:为什么每个组查询的最大 N 个成员有效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！