MySQL左连接时如何仅匹配首行，其余行对应字段填充为NULL？

解决方案 #

核心思路是给每个动物对应的场地记录增加行号标记，仅在第一条场地记录（或无场地记录）时展示销售数据，其余行返回NULL。

适用MySQL 8.0+/MariaDB 10.2+ 版本（支持窗口函数） #

修改后的查询语句如下：

SELECT 
    a.*,
    p.place,
    p.amount AS amount_in_place,
    IF(p.row_num = 1 OR p.place IS NULL, s.sales, NULL) AS sales
FROM animals AS a 
LEFT JOIN (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY place) AS row_num
    FROM places
) AS p ON a.name = p.name 
LEFT JOIN (
    SELECT 
        GROUP_CONCAT("Amount: ", amount, " and price: ", price SEPARATOR ", ") AS sales, 
        name 
    FROM in_sale 
    GROUP BY name
) AS s ON s.name = a.name 
ORDER BY a.name, p.row_num;

逻辑说明

1. 子查询p给places表按动物名称分组，给每个分组内的场地记录生成递增行号，默认按place字段排序，你可以根据实际需求修改ORDER BY后的排序规则（比如按amount排序）。
2. 用IF判断：仅当当前行是该动物的第一条场地记录，或该动物没有对应的场地记录时，才返回sales的值，其余场景返回NULL，完全匹配期望的输出结果，同时也满足无场地记录的动物正常展示销售数据的要求。

兼容MySQL 5.x 版本（不支持窗口函数） #

如果使用的是低版本MySQL，可以用变量模拟行号，语句如下：

SELECT 
    a.*,
    p.place,
    p.amount AS amount_in_place,
    IF(p.row_num = 1 OR p.place IS NULL, s.sales, NULL) AS sales
FROM animals AS a 
LEFT JOIN (
    SELECT 
        *,
        @row := IF(@pre_name = name, @row + 1, 1) AS row_num,
        @pre_name := name
    FROM places, (SELECT @row := 0, @pre_name := '') AS init
    ORDER BY name, place
) AS p ON a.name = p.name 
LEFT JOIN (
    SELECT 
        GROUP_CONCAT("Amount: ", amount, " and price: ", price SEPARATOR ", ") AS sales, 
        name 
    FROM in_sale 
    GROUP BY name
) AS s ON s.name = a.name 
ORDER BY a.name, p.row_num;

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