sql - 尝试在列上分组，同时通过排序选择所有其他信息

Question

我在构建使用以下条件的查询时遇到了一些麻烦:

1. 与组织匹配
2. 先按分数 (desc) 排序，然后按句柄 (asc) 排序
3. 按类型分组

所以这个查询是我的起点:

select * from social_media_handles where org = '00000001' order by score desc, handle asc;

这将给我以下数据......然后我需要按类型分组，所以我只提取最匹配的 social_media_handles。

   org    |                            handle                             |                   url                   |   type   |      score      | dataset_date
----------+---------------------------------------------------------------+-----------------------------------------+----------+-----------------+--------------
 00000001 | boathousesw15                                                 | http://www.boathouseputney.co.uk        | twitter  | 500111972000056 | 2013-10-15
 00000001 | aspall                                                        | http://www.boathouseputney.co.uk        | twitter  | 500111972000018 | 2013-10-15
 00000001 | nathansloane                                                  | http://www.boathouseputney.co.uk        | twitter  | 500111972000018 | 2013-10-15
 00000001 | youngspubs                                                    | http://www.boathouseputney.co.uk        | twitter  | 500111972000018 | 2013-10-15
 00000001 | pages/the-boathouse-putney/153429008029137                    | http://www.boathouseputney.co.uk        | facebook | 500111972000011 | 2013-10-15
 00000001 | putneysocial                                                  | http://www.boathouseputney.co.uk        | twitter  | 500111972000009 | 2013-10-15
 00000001 | theexchangesw15                                               | http://www.boathouseputney.co.uk        | twitter  | 500111972000009 | 2013-10-15
 00000001 | youngspubs                                                    | http://www.youngshotels.co.uk           | twitter  | 500111970000016 | 2013-10-15

预期输出

   org    |                            handle                             |                   url                   |   type   |      score      | dataset_date
----------+---------------------------------------------------------------+-----------------------------------------+----------+-----------------+--------------
 00000001 | boathousesw15                                                 | http://www.boathouseputney.co.uk        | twitter  | 500111972000056 | 2013-10-15
 00000001 | pages/the-boathouse-putney/153429008029137                    | http://www.boathouseputney.co.uk        | facebook | 500111972000011 | 2013-10-15

我尝试过group by、distinct 和子查询，但运气不佳。是否有解决此问题的模式？

我正在使用 Postgres 并通过 distinct on 解决了这个问题，但我正在寻找与不同供应商兼容的版本。

Answer 1

这个问题在 SO 上经常出现，通常被赋予标签 greatest-n-per-group (在您的情况下，n=1)。

以下是一些适用于 MySQL 的常见解决方案:

SELECT h.*
FROM social_media_handles AS h
JOIN (
    SELECT type, MAX(score) AS score 
    FROM social_media_handles WHERE org = '00000001' 
    GROUP BY type) AS maxh USING (type, score)
WHERE org = '00000001' 
ORDER BY score DESC, handle ASC;

第二种解决方案不使用子查询或分组依据。它尝试将行 h1 与具有相同 type 和 org 但具有更高 score 的假设行 h1 匹配。如果不存在具有更高分数的行 h2，则 h1 必须具有最高分数的行。

SELECT h1.*
FROM social_media_handles AS h1
LEFT OUTER JOIN social_media_handles AS h2
 ON h1.type = h2.type AND h1.org = h2.org AND h1.score < h2.score
WHERE h1.org = '00000001'
 AND h2.score IS NULL
ORDER BY h1.score DESC, h1.handle DESC;

哪种解决方案最快？这取决于。根据数据集的大小、不同类型的数量等，我发现这两种解决方案的效果都更好。因此您应该测试这两种解决方案，看看哪种解决方案更适合您的情况。

@Roman Pekar 展示的 CTE 解决方案也适用于支持 CTE 语法的 RDBMS。其中包括 PostgreSQL、Oracle、Microsoft SQL Server、IBM DB2 等。

MySQL 和 SQLite 是唯一仍然不支持 CTE 语法的广泛使用的数据库。