目前我有 2 個表，第一個表顯示狀態計數，refno。和 agent_id（負責參考號的人），第二個表有一個 id 和 agent_name。所以要在 refno 旁邊推薦一個特定的代理。在表 1 中，您可以通過代理表的 id 參考它。
dbfiddle: 
uj5u.com熱心網友回復：
你需要LEFT加入的listings到agents，如果你想在結果的行listings不具有匹配id的agents。
此外，您必須分組COALESCE(t.agent_id, 0)以涵蓋0和nullin 的兩種情況agent_id：
SELECT COALESCE(c.name, 'Unassigned') name,
SUM(CASE WHEN t.status = 'D' THEN 1 ELSE 0 END) AS draft,
SUM(CASE WHEN t.status = 'N' THEN 1 ELSE 0 END) AS unpublish,
SUM(CASE WHEN t.status = 'Y' THEN 1 ELSE 0 END) AS publish,
SUM(CASE WHEN t.status = 'U' THEN 1 ELSE 0 END) AS action,
SUM(CASE WHEN t.status = 'L' THEN 1 ELSE 0 END) AS unlisted,
SUM(CASE WHEN t.status = 'S' THEN 1 ELSE 0 END) AS sold,
SUM(CASE WHEN t.status = 'T' THEN 1 ELSE 0 END) AS let,
SUM(CASE WHEN t.status IN ('D', 'N', 'Y', 'U', 'L', 'S', 'T') THEN 1 ELSE 0 END) AS total
FROM listings t LEFT JOIN agents c
ON t.agent_id = c.id
GROUP BY COALESCE(t.agent_id, 0), c.name
ORDER BY c.name IS NULL, c.name;
我ELSE 0在所有CASE運算式中添加了一個部分，以便您0在結果中獲得s 而不是NULLs 并total通過使用運算子將列的運算式更改為僅 1 SUM IN，但是如果 'D', 'N', 'Y', 'U ', 'L', 'S' 和 'T' 是statusthen唯一可能的值，您可以使用COUNT(*)：
SELECT COALESCE(c.name, 'Unassigned') name,
SUM(CASE WHEN t.status = 'D' THEN 1 ELSE 0 END) AS draft,
SUM(CASE WHEN t.status = 'N' THEN 1 ELSE 0 END) AS unpublish,
SUM(CASE WHEN t.status = 'Y' THEN 1 ELSE 0 END) AS publish,
SUM(CASE WHEN t.status = 'U' THEN 1 ELSE 0 END) AS action,
SUM(CASE WHEN t.status = 'L' THEN 1 ELSE 0 END) AS unlisted,
SUM(CASE WHEN t.status = 'S' THEN 1 ELSE 0 END) AS sold,
SUM(CASE WHEN t.status = 'T' THEN 1 ELSE 0 END) AS let,
COUNT(*) AS total
FROM listings t LEFT JOIN agents c
ON t.agent_id = c.id
GROUP BY COALESCE(t.agent_id, 0), c.name
ORDER BY c.name IS NULL, c.name;
請參閱演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/322116.html