如何编写MySQL查询按客户和物料返回指定最新值

表结构 #

CREATE TABLE your_table_name (
    id INT PRIMARY KEY,
    client VARCHAR(255),
    dateOne DATE,
    descOne FLOAT,
    material VARCHAR(255),
    dateTwo DATE,
    descTwo FLOAT
);

示例数据 #

client        dateOne    descOne  material  dateTwo    descTwo
1000009999    30.09.2020 60       Null      NULL       0
1000009999    18.08.2020 70       Null      NULL       0
1000009999    06.07.2018 50       Null      NULL       0
1000009999    30.09.2020 60       1000004   30.09.2020 60
1000009999    30.09.2020 60       1000004   17.11.2020 55
1000009999    30.09.2020 60       1000004   21.03.2022 60
1000009999    30.09.2020 60       1000004   19.04.2022 60
1000009999    30.09.2020 60       1000002   30.09.2020 60
1000009999    30.09.2020 60       1000002   17.11.2020 55
1000009999    30.09.2020 60       1000002   21.03.2022 60
1000009999    30.09.2020 60       1000002   19.04.2022 60
1000009999    30.09.2020 60       1000001   30.09.2020 60
1000009999    30.09.2020 60       1000001   17.11.2020 55
1000001163    30.09.2020 60       Null      NULL       0
1000001163    18.08.2020 70       Null      NULL       0
1000001163    06.07.2018 50       Null      NULL       0
1000001163    30.09.2020 60       1000004   30.09.2020 60
1000001163    30.09.2020 60       1000004   17.11.2020 55
1000001163    30.09.2020 60       1000004   21.03.2022 60
1000001163    30.09.2020 60       1000004   19.04.2022 60
1000001163    30.09.2020 60       1000002   30.09.2020 60
1000001163    30.09.2020 60       1000002   17.11.2020 55
1000001163    30.09.2020 60       1000002   21.03.2022 60
1000001163    30.09.2020 60       1000002   19.04.2022 60
1000001163    30.09.2020 60       1000001   30.09.2020 60
1000001163    30.09.2020 60       1000001   17.11.2020 55

查询需求 #

• 按client和material分组返回数据
• 当material不为空时，返回该分组下dateTwo最新的那一行的descTwo值
• 当material为空时，返回该分组下dateOne最新的那一行的descOne值

预期结果 #

client        dateOne    descOne  material  dateTwo    descTwo
1000009999    30.09.2020 60       Null      NULL       0
1000009999    30.09.2020 60       1000004   19.04.2022 60
1000009999    30.09.2020 60       1000002   19.04.2022 60
1000009999    30.09.2020 60       1000001   17.11.2020 55
1000001163    30.09.2020 60       Null      NULL       0
1000001163    30.09.2020 60       1000004   19.04.2022 60
1000001163    30.09.2020 60       1000002   19.04.2022 60
1000001163    30.09.2020 60       1000001   17.11.2020 55

解决方案 #

使用窗口函数ROW_NUMBER()对每个分组内的记录按指定日期排序，然后筛选出排序后行号为1的记录，即为每个分组的最新记录：

SELECT client, dateOne, descOne, material, dateTwo, descTwo
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER(
            PARTITION BY client, material
            ORDER BY 
                CASE WHEN material IS NOT NULL THEN dateTwo ELSE dateOne END DESC
        ) AS rn
    FROM your_table_name
) t
WHERE rn = 1;

说明 #

• PARTITION BY client, material：按客户和物料分组
• ORDER BY CASE WHEN material IS NOT NULL THEN dateTwo ELSE dateOne END DESC：根据material是否为空选择排序字段，非空时按dateTwo降序，为空时按dateOne降序
• 外层查询筛选rn=1的记录，得到每个分组的最新一条数据

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