sql - 在 SQL 表中查找所有两列重复项，其中第三列的后面值大于前面的值

Question

对 SQL 来说相当新，如果能更巧妙地解决这个问题，那就太好了。

这是一个示例表：

col1	col2	col3	日期
1	1	2	2011-01-22
1	1	4	2011-01-27
3	3	2	2011-01-20
3	8	4	2011-01-12
3	8	2	2011-01-30
4	1	3	2011-01-09
5	3	3	2011-01-27
5	4	2	2011-01-22

我想返回所有不同的 col1, col2 值

• col1 和 col2 值相同（重复）并且
• 最新的 col3 值大于最早的值

在这种情况下，结果应该是

col1	第 2 栏
1	1

我可以

FROM table
GROUP BY col1, col2
HAVING count(*) > 1

要获得重复的 col1、col2 元组，但从那里我的解决方案变得丑陋，使用多个嵌套子查询，有时重复相同的子查询。

什么是干净的方法来做到这一点？它在 SQLite 中。

标签： sqlsqlite

Answer 1

我会ROW_NUMBER在这里使用旋转逻辑：

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY col1, col2
                           ORDER BY date) rn
    FROM yourTable
)

SELECT col1, col2
FROM cte
GROUP BY col1, col2
HAVING MAX(CASE WHEN rn = 2 THEN col3 END) >
       MAX(CASE WHEN rn = 1 THEN col3 END);

演示

这种方法非常接近您目前正在做的事情。唯一添加的是一个HAVING子句，它将较新的col3值与较旧的值进行比较。请注意，此答案假定您的“重复”只会成对出现，而不是三胞胎等。

编辑：

如果由于某种原因，您真的不能使用窗口函数，例如因为您使用的是非常旧的 SQLite 版本，我们仍然可以大致使用上述方法进行连接：

SELECT t1.col1, t1.col2
FROM yourTable t1
INNER JOIN
(
    SELECT col1, col2, MIN(date) AS min_date, MAX(date) AS max_date
    FROM yourTable
    GROUP BY col1, col2
) t2
    ON t2.col1 = t1.col1 AND t2.col2 = t1.col2
GROUP BY t1.col1, t1.col2
HAVING
    MAX(CASE WHEN t1.date = t2.max_date THEN col3 END) >
    MAX(CASE WHEN t1.date = t2.min_date THEN col3 END);

演示