SQLite3 中的窗口函数

问题描述：

以下 Oracle SQL select 允许我根据某些字段选择重复表中的所有行，例如，它们具有相同的 COLUMN_1、COLUMN_2 和 COLUMN_3

The following Oracle SQL select allows me to select all the rows of a table that are duplicated according to some fields, eg, they have the same COLUMN_1, COLUMN_2 and COLUMN_3

SELECT  *
FROM    (
        SELECT  t.*, ROW_NUMBER() OVER (PARTITION BY COLUMN_1, COLUMN_2, COLUMN_3 ORDER BY COLUMN_1) AS rn
        FROM    MY_TABLE t
        )
WHERE   rn > 1;

如何在 sqlite3 中做同样的事情?

How to do the very same in sqlite3?

答

下面的查询可以让你接近你想要的:

The following query can get you close to what you want:

SELECT t1.*
FROM MY_TABLE t1
LEFT JOIN
(
    SELECT COLUMN_2, COLUMN_3, MIN(COLUMN_1) AS MIN_COLUMN_1
    FROM MY_TABLE
    GROUP BY COLUMN_2, COLUMN_3
) t2
    ON t1.COLUMN_2 = t2.COLUMN_2 AND
       t1.COLUMN_3 = t2.COLUMN_3 AND
       t1.COLUMN_1 = t2.MIN_COLUMN_1
WHERE
    t2.COLUMN_2 IS NULL;

此查询的工作原理是过滤掉每个 COLUMN_2、COLUMN_3 组具有最小 COLUMN_1 值的记录.实际上，这对应的是排名而不是行号，但也许这对您来说是可以接受的.

This query works by filtering off records having the minimum COLUMN_1 value for each COLUMN_2, COLUMN_3 group. Actually, this corresponds to the rank rather than the row number, but maybe this is acceptable to you.

相关推荐