SQL学习咨询：内连接查询扩展字段及多客户名列表生成

嘿，针对你提到的两个问题，我给你整理了具体的解决方案：

1. 同时输出匹配与不匹配的关联记录 #

你原来的INNER JOIN只会返回tw.model_id和model.id完全匹配的记录。如果要同时包含不匹配的记录（比如tw中符合条件但model_id在model表中找不到对应值，或者model表中有id但tw中没有对应model_id的情况），可以根据你使用的数据库选择以下方案：

方案A：支持FULL OUTER JOIN的数据库（如PostgreSQL、SQL Server） #

直接用全外连接，同时保留两边不匹配的记录，再加上你的过滤条件：

SELECT * 
FROM tw 
FULL OUTER JOIN model ON model.id = tw.model_id 
WHERE (tw.completed = 1 AND tw.stock = 0) OR tw IS NULL 
ORDER BY COALESCE(model.id, tw.model_id);

这里COALESCE用来处理排序时的NULL值，确保结果有序。

方案B：不支持FULL OUTER JOIN的数据库（如MySQL） #

用LEFT JOIN和RIGHT JOIN结合UNION来模拟全外连接：

-- 先取tw中符合条件的所有记录（包括model不匹配的）
SELECT * 
FROM tw 
LEFT JOIN model ON model.id = tw.model_id 
WHERE tw.completed = 1 AND tw.stock = 0

UNION

-- 再取model中没有对应tw记录的部分
SELECT * 
FROM tw 
RIGHT JOIN model ON model.id = tw.model_id 
WHERE tw IS NULL
ORDER BY COALESCE(model.id, tw.model_id);

如果只需要tw中符合条件但model不匹配的记录（不需要model表中无对应tw的记录），直接用LEFT JOIN即可：

SELECT * 
FROM tw 
LEFT JOIN model ON model.id = tw.model_id 
WHERE tw.completed = 1 AND tw.stock = 0 
ORDER BY COALESCE(model.id, tw.model_id);

2. 生成多个customer_name的列表 #

如果同一条主记录对应多个customer_name，可以用字符串聚合函数来生成逗号分隔（或自定义分隔符）的列表，不同数据库的函数略有不同：

示例（MySQL/MariaDB）：用GROUP_CONCAT #

假设customer_name来自tw表，按tw的主键分组聚合：

SELECT 
    tw.id,
    tw.model_id,
    model.name AS model_name,
    GROUP_CONCAT(tw.customer_name SEPARATOR ', ') AS customer_list
FROM tw 
LEFT JOIN model ON model.id = tw.model_id 
WHERE tw.completed = 1 AND tw.stock = 0 
GROUP BY tw.id, tw.model_id, model.name
ORDER BY model.id;

如果需要去重，可以加上DISTINCT：GROUP_CONCAT(DISTINCT tw.customer_name SEPARATOR ', ')

示例（PostgreSQL）：用STRING_AGG #

SELECT 
    tw.id,
    tw.model_id,
    model.name AS model_name,
    STRING_AGG(tw.customer_name, ', ') AS customer_list
FROM tw 
LEFT JOIN model ON model.id = tw.model_id 
WHERE tw.completed = 1 AND tw.stock = 0 
GROUP BY tw.id, tw.model_id, model.name
ORDER BY model.id;

示例（SQL Server）：用STRING_AGG（2017及以上版本） #

SELECT 
    tw.id,
    tw.model_id,
    model.name AS model_name,
    STRING_AGG(tw.customer_name, ', ') AS customer_list
FROM tw 
LEFT JOIN model ON model.id = tw.model_id 
WHERE tw.completed = 1 AND tw.stock = 0 
GROUP BY tw.id, tw.model_id, model.name
ORDER BY model.id;

前端展示列表 #

如果需要在前端把聚合后的字符串转成HTML列表（比如<ul>），可以在PHP中处理：

while ($row = $result->fetch_assoc()) {
    echo "<tr>";
    echo "<td>" . $row['id'] . "</td>";
    echo "<td>" . $row['model_name'] . "</td>";
    // 处理customer列表
    echo "<td><ul>";
    $customers = explode(', ', $row['customer_list']);
    foreach ($customers as $cust) {
        echo "<li>" . htmlspecialchars($cust) . "</li>";
    }
    echo "</ul></td>";
    echo "</tr>";
}

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