Window functions in SQLite3

问题:

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;

How to do the very same in sqlite3?

回答1:

You can use rowid and a correlated subquery:

select t.*
from (select t.*,
             (select count(*)
              from my_table t2
              where t2.column_1 = t.column_1 and t2.column_2 = t.column_2 and
                    t2.column_3 = t.column_3 and t2.rowid <= t.rowid
             ) as seqnum
      from my_table t
     ) t
where seqnum > 1;

You can use any unique key instead of rowid, if you have one in the table.

回答2:

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;

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.