mysql - 使用 GROUP BY 将查询从 MySQL 转换为 Postgres 和 SQLite

Question

在 MySQL 中，我使用了很多如下所示的结构:

LEFT JOIN(
    SELECT field_one, field_two, field_three FROM (
        SELECT field_one, field_two, field_three FROM table_one
        ORDER BY field_three 
    ) o GROUP BY field_one
) abc ON cur_table.some_field = abc.field_one

我使用它，因为它帮助我实现一个常见的任务 - 在分组之前对一些数据进行排序。现在我想知道如何将此 SQL 构造从 MySQL 迁移到 SQLite 和 PostgreSQL。

Answer 1

我怀疑您的查询的目的是使用 MySQL 怪癖为未聚合列选择任意行，这些列也未在现有 GROUP BY 子句中列出(这违反了 SQL 标准大多数其他 RDBMS 不支持)。通过在子查询中排序，您可以让 MySQL 选择每个组中 field_three 最小的行，所以我假设您想要:

每个 field_three 的最小 field_one 以及来自同一行的 field_two 。

您的原始查询在遵循 SQL 标准的 Postgres 中无法工作。如果 SELECT 具有 GROUP BY 子句，则必须列出或聚合所有输出列。考虑:

• How do I port query with GROUP BY clause to PostgreSQL?

带窗口函数的标准 SQL

一种可能的标准 SQL 解决方案是在子查询中使用窗口函数 row_number():

SELECT field_one, field_two, field_three
FROM  (
   SELECT field_one, field_two, field_three
        , row_number() OVER(PARTITION BY field_one ORDER BY field_three) rn
   FROM   table_one
   ) sub
WHERE sub.rn = 1

适用于 Postgres，但不适用于尚不支持窗口函数的 SQLite 或 MySQL。

基本标准SQL

此查询适用于所有三个 RDBMS(以及几乎任何其他地方)，但需要 field_three 中唯一的最大值(如果每个 field_three 的最大 field_one 存在关联，则返回多行)。

SELECT t1.*
FROM   table_one t1
LEFT   JOIN table_one t2 ON t1.field_one = t2.field_one
                        AND t1.field_three < t2.field_three
WHERE  t2.field_one IS NULL

如果您有任何唯一的(一组)列，则可以解决关系问题，但它很笨拙。相关:

• Fetch the row which has the Max value for a column

Postgres

(除了支持所有标准 SQL 解决方案)Postgres 还具有强大的 DISTINCT ON (标准 DISTINCT 的扩展，但不像 MySQL 和 SQLite 那样违反标准):

SELECT DISTINCT ON (field_one)
       field_one, field_two, field_three
FROM   table_one
ORDER  BY field_one, field_three

您可以通过向 ORDER BY 添加更多列来解决关系。详情:

• Select first row in each GROUP BY group?

SQLite

...与MySQL有类似的怪癖(违反了SQL标准)。 From the release notes:

Queries of the form: "SELECT max(x), y FROM table" returns the value of y on the same row that contains the maximum x value.

所以:

SELECT field_one, field_two, max(field_three) AS field_three
FROM   table_one
GROUP  BY field_one

field_two 取自 max(field_three) 行。相关:

• How can I select the set of rows where each item has the greatest timestamp?

<小时/>

连接和连接条件在任何地方都一样:

LEFT JOIN (SELECT ...) abc ON cur_table.some_field = abc.field_one