MySQL查询需求：按number去重并优先保留非null的image字段

数据表包含number和image字段，image为非必填字段，允许为null。需要执行查询时，每个number仅返回一行结果，优先保留image不为null的行。

当前使用的SQL语句 #

SELECT DISTINCT number, image FROM table ORDER BY number ASC

当前查询结果 #

[
    {
        "number": 1,
        "image": null
    },
    {
        "number": 1,
        "image": "https://example.com/image1.png"
    },
    {
        "number": 2,
        "image": null
    },
    {
        "number": 2,
        "image": "https://example.com/image2.png"
    },
    {
        "number": 3,
        "image": "https://example.com/image3.png"
    },
    {
        "number": 3,
        "image": null
    },
    {
        "number": 4,
        "image": null
    }
]

期望查询结果 #

[
    {
        "number": 1,
        "image": "https://example.com/image1.png"
    },
    {
        "number": 2,
        "image": "https://example.com/image2.png"
    },
    {
        "number": 3,
        "image": "https://example.com/image3.png"
    },
    {
        "number": 4,
        "image": null
    }
]

解决方案 #

方案1：窗口函数法（适用于MySQL 8.0+、PostgreSQL、SQL Server等） #

使用ROW_NUMBER()窗口函数按number分组，给image非null的行赋予优先排序的序号，再筛选每个分组的第一行：

SELECT number, image
FROM (
    SELECT 
        number, 
        image,
        ROW_NUMBER() OVER (
            PARTITION BY number 
            ORDER BY CASE WHEN image IS NOT NULL THEN 0 ELSE 1 END
        ) AS rn
    FROM your_table
) t
WHERE rn = 1
ORDER BY number ASC;

方案2：聚合函数法（适用于所有支持GROUP BY的数据库，包括MySQL 5.x） #

利用MAX()函数会优先选取非null值的特性，直接按number分组聚合：

SELECT number, MAX(image) AS image
FROM your_table
GROUP BY number
ORDER BY number ASC;

注：当某个number存在非null的image时，MAX(image)会返回该非null值；仅当所有行的image都是null时，才返回null，完全匹配需求。

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