查询重复数据中值最大的记录，自连接查询返回错误结果求助

看起来你是要找出所有key和对应的code，要求同一个key下，当code值更大时，对应的date不能小于更小code的date——就像你说的Key8，因为code2的date比code1的小，所以不符合条件要排除对吧？

下面给你两种可靠的查询方法，适配大部分主流数据库：

方法1：用窗口函数（推荐，简洁高效） #

窗口函数能很方便地对比同一key下不同code的日期。我们可以用LAG()函数拿到当前code的上一个（更小的）code对应的日期，然后筛选出符合要求的记录：

WITH ranked_records AS (
    SELECT 
        `key`,
        code,
        `date`,
        -- 按code升序，获取同一key下前一条记录的date
        LAG(`date`) OVER (PARTITION BY `key` ORDER BY code) AS prev_code_date
    FROM your_table
)
SELECT `key`, code
FROM ranked_records
-- 第一个code没有上一条记录，直接保留；其他code要求当前date >= 上一个code的date
WHERE prev_code_date IS NULL OR `date` >= prev_code_date;

如果你的数据库版本比较旧（比如MySQL 5.7及以前）不支持CTE，换成子查询写法就行：

SELECT `key`, code
FROM (
    SELECT 
        `key`,
        code,
        `date`,
        LAG(`date`) OVER (PARTITION BY `key` ORDER BY code) AS prev_code_date
    FROM your_table
) AS ranked_records
WHERE prev_code_date IS NULL OR `date` >= prev_code_date;

方法2：自连接排除不合格的Key #

要是你不想用窗口函数，也可以先通过自连接找出所有存在问题的key（也就是有code小但date反而更大的情况），再排除这些key的所有记录：

-- 先找出所有不符合条件的key
WITH invalid_keys AS (
    SELECT DISTINCT t1.`key`
    FROM your_table t1
    JOIN your_table t2 
        ON t1.`key` = t2.`key` 
        AND t1.code < t2.code 
        AND t1.`date` > t2.`date`
)
-- 筛选出不在不合格列表里的key和code
SELECT `key`, code
FROM your_table
WHERE `key` NOT IN (SELECT `key` FROM invalid_keys);

同样，不支持CTE的话改成子查询：

SELECT `key`, code
FROM your_table
WHERE `key` NOT IN (
    SELECT DISTINCT t1.`key`
    FROM your_table t1
    JOIN your_table t2 
        ON t1.`key` = t2.`key` 
        AND t1.code < t2.code 
        AND t1.`date` > t2.`date`
);

为啥你之前的自连接会出错？ #

你之前用自连接得到错误结果，大概率是没处理好逻辑：比如如果直接自连接筛选符合条件的记录，很容易重复返回数据，或者漏掉第一个code的记录；而上面的方法2是先定位所有有问题的key，再整体排除，这样就能保证结果准确啦。

内容的提问来源于stack exchange，提问作者user3007447