MySQL内连接查询时第二个表多条数据仅取1行并标记multi的实现问题

解决方案 #

方案1：兼容MySQL 5.x/8.x 通用版本

核心逻辑是按exchange表的主键xid分组，统计每个xid关联的exchitems记录数，通过CASE判断返回对应字段值，完全符合ONLY_FULL_GROUP_BY语法规范：

SELECT 
    a.xid,
    a.cusid,
    a.xdate,
    a.xref,
    CASE WHEN COUNT(b.chid) > 1 THEN 'multi' ELSE MAX(b.xcur) END AS xcur,
    CASE WHEN COUNT(b.chid) > 1 THEN 0 ELSE MAX(b.xsell) END AS xsell,
    CASE WHEN COUNT(b.chid) > 1 THEN 0 ELSE MAX(b.xbuy) END AS xbuy,
    a.xtotal
FROM exchange a
INNER JOIN exchitems b ON a.xid = b.xid AND a.cusid = b.cusid
WHERE a.cusid = 1
GROUP BY a.xid, a.cusid, a.xdate, a.xref, a.xtotal
ORDER BY a.xdate DESC, a.xid DESC

当关联子表记录数为1时，MAX()聚合函数取到的就是唯一一条子记录的字段值；当记录数大于1时，直接返回约定的multi和0值，返回结果和你预期完全一致。

方案2：MySQL 8.0+ 窗口函数版本

如果你使用的是MySQL 8.0及以上版本，也可以用CTE+窗口函数实现，逻辑更直观：

WITH items_with_cnt AS (
    SELECT 
        b.*,
        COUNT(*) OVER(PARTITION BY xid) AS item_cnt,
        ROW_NUMBER() OVER(PARTITION BY xid ORDER BY chid) AS rn
    FROM exchitems b
    WHERE b.cusid = 1
)
SELECT 
    a.xid,
    a.cusid,
    a.xdate,
    a.xref,
    CASE WHEN c.item_cnt > 1 THEN 'multi' ELSE c.xcur END AS xcur,
    CASE WHEN c.item_cnt > 1 THEN 0 ELSE c.xsell END AS xsell,
    CASE WHEN c.item_cnt > 1 THEN 0 ELSE c.xbuy END AS xbuy,
    a.xtotal
FROM exchange a
INNER JOIN items_with_cnt c ON a.xid = c.xid AND a.cusid = c.cusid
WHERE c.rn = 1
ORDER BY a.xdate DESC, a.xid DESC

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