mysql - 子选择的 MySQL 排名 - 按组排列的前 N 个结果
问题描述
我需要获取每个国家/地区前 2 个名称的列表(来自 Accounts & Country Table)。我搜索了很多,也找到了一些有效的答案,但无法得到正确的结果。
请在此处查看我的 SQL Fiddle:
http://sqlfiddle.com/#!9/cd1296/5
CREATE TABLE IF NOT EXISTS `country` (
`id` int(6) unsigned NOT NULL,
`iso` varchar(3) NOT NULL,
`country_name` varchar(24) NOT NULL,
PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;
INSERT INTO `country` (`id`, `iso`,`country_name`) VALUES
('1', 'DEU','Germany'),
('2', 'USA','United States'),
('3', 'CAN','Canada'),
('4', 'JPN','Japan');
CREATE TABLE IF NOT EXISTS `accounts` (
id int(6) unsigned NOT NULL,
name varchar(50) NOT NULL,
iso3 varchar(3) NOT NULL,
PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `accounts` (`id`,`name`, `iso3`) VALUES
('1', 'Hans', 'DEU'),
('2', 'Willi', 'DEU'),
('3', 'Peter', 'DEU'),
('4', 'Susanne', 'DEU'),
('5', 'John', 'USA'),
('6', 'Jane', 'USA'),
('7', 'Peter', 'USA'),
('8', 'Paul', 'USA'),
('9', 'Mary', 'USA'),
('10', 'Gerard', 'CAN'),
('11', 'Mirelle', 'CAN'),
('12', 'Hiko', 'JPN'),
('13', 'Miko', 'JPN'),
('14', 'Susanne', 'DEU'),
('15', 'Peter', 'DEU'),
('16', 'John', 'USA'),
('17', 'Paul', 'USA'),
('18', 'Susanne', 'DEU'),
('19', 'Bob', 'DEU'),
('20', 'John', 'USA'),
('21', 'Paul', 'USA'),
('33', 'Gerard', 'CAN'),
('22', 'Maribelle', 'CAN'),
('23', 'Gerd', 'CAN'),
('24', 'Mira', 'CAN'),
('25', 'Huko', 'JPN'),
('26', 'Hako', 'JPN'),
('27', 'Hiko', 'JPN'),
('28', 'Jon', 'USA'),
('29', 'Jim', 'USA'),
('30', 'John', 'USA'),
('31', 'JJ', 'USA'),
('32', 'Bob', 'USA'),
('34', 'Bob', 'USA'),
('35', 'Miko', 'JPN'),
('36', 'Miko', 'JPN');
使用此语句会使列表按正确的顺序排列,但不会在第二个结果之后停止:
SELECT country_name, iso, name, COUNT(name) AS name_count
FROM accounts
JOIN country ON country.iso = accounts.iso3
GROUP BY country.iso, name
ORDER BY country.iso ASC, name_count DESC;
正如其他问题/答案中所建议的那样,解决方案可以使用“MySQL 会话变量”(基于https://www.databasejournal.com/features/mysql/selecting-the-top-n-results-by-group-in- mysql.html )。
我的问题:country_rank 没有正确填充,因此没有给出正确的结果。我做错了什么?
SET @current_country = "";
SET @country_rank = 0;
SELECT country_name, name, name_count, rank
FROM
(
SELECT country_name, iso, name, COUNT(name) AS name_count,
@country_rank := IF( @current_country = iso,
@country_rank + 1,
1
) AS rank,
@current_country := iso
FROM accounts
JOIN country ON country.iso = accounts.iso3
GROUP BY country.iso, name
ORDER BY country.iso ASC, name_count DESC
) AS ranked
WHERE rank<=2;
解决方案
您需要在子查询中进行分组,以便对分组结果进行排名。
SELECT country_name, name, name_count, rank
FROM (
SELECT country_name, iso, name, name_count,
@country_rank := IF( @current_country = iso,
@country_rank + 1,
1
) AS rank,
@current_country := iso
FROM (
SELECT country_name, iso, name, COUNT(name) AS name_count
FROM accounts
JOIN country ON country.iso = accounts.iso3
GROUP BY country.iso, name
ORDER BY country.iso ASC, name_count DESC
) AS ordered
) AS ranked
CROSS JOIN (SELECT @country_rank = 0, @current_country = '') AS vars
WHERE rank<=2;
推荐阅读
- mongodb - MongoDB:跨文档列表中的项目不同
- html - Angular/Html - 如何使整个单元格可点击?
- java - Quarkus Panache Mongodb 可能的查询
- gridview - Yii2 在 Accordion Widget 中显示动态 GridView
- git - TeamCity 根据拉取请求构建特定分支
- matrix - 将自定义矩阵转换为 PxTransform 的 Physx 3.4 (Linux) 问题
- excel - 使用 VBA 将公式插入 Excel 时预期语句结束错误
- java - containsKey() 总是返回 true
- c# - 通过 system.io 扫描我的 C:\ 驱动器时出现问题
- build - 错误:CSS 缩小错误:JisonLexerError:第 1 行的词法错误:无法识别的文本