SELECT sg.date, sg.groupName, sg.highlights,
user.display_name as displayName,
COUNT(uc.groupName) as cmtcnt
FROM `saved_groups` as sg
LEFT JOIN `user` on user.email = sg.userName
LEFT JOIN `user_comments` as uc on sg.groupName = uc.groupName
WHERE uc.deleted=0
GROUP BY sg.groupName
我有兩個表，saved_groups并user_comments和查詢的第二行應該（做，它的作業原理）以及與每個組相關聯的評論的數量回傳一個額外的列。
但是，當一個組的相關評論為零時，該組將不會被回傳。回傳的唯一行是那些有> 0評論的行。期望的行為是回傳所有 groupNames，并為 uc 表中具有零關聯注釋的行指定 0。
如何修復此查詢？
我試過：IF(uc.deleted=1, 0, COUNT(uc.groupName)) as cmtcnt- 但這沒有區別，回傳相同的結果。
在這一點上，我不確定接下來要嘗試什么。
建議？
更新：
試過這個：
SELECT sg.date, sg.groupName, sg.highlights,
user.display_name as displayName,
COUNT(uc.groupName) as cmtcnt
FROM geom.saved_groups as sg
JOIN geom.user on user.email = sg.userName
JOIN geom.user_comments as uc on sg.groupName = uc.groupName
WHERE isnull(uc.deleted,0) in (0,1)
GROUP BY sg.groupName
得到了：
#1582 - Incorrect parameter count in the call to native function 'isnull'
uj5u.com熱心網友回復：
你想要所有的記錄嗎？然后洗掉WHERE子句。你想要0沒有計數的記錄？使用COALESCE. 像這樣：
SELECT sg.date
, sg.groupName
, sg.highlights
, user.display_name as displayName
, COALESCE(COUNT(uc.groupName), 0) as cmtcnt
FROM `saved_groups` as sg
LEFT JOIN `user` on user.email = sg.userName
LEFT JOIN `user_comments` as uc on sg.groupName = uc.groupName
AND uc.deleted = 0 -- to get only comments that have not been deleted, and not the deleted ones
GROUP BY sg.groupName
uj5u.com熱心網友回復：
您的 where 條件正在過濾記錄并只回傳那些有資料的記錄，uc因此沒有左連接的好處。你可以做 (uc.deleted=0 or uc.deleted is null)
或 Move the condition uc.deleted with on condition with join
uj5u.com熱心網友回復：
檢查 uc.deleted=1
SELECT sg.date, sg.groupName, sg.highlights,
user.display_name as displayName,
COUNT(uc.groupName) as cmtcnt
FROM dbo.saved_groups as sg
JOIN dbo.user on user.email = sg.userName
LEFT JOIN dbo.user_comments as uc on sg.groupName = uc.groupName
WHERE uc.deleted =1
GROUP BY sg.groupName
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/363119.html
上一篇：SQL-使用now()減去一天
下一篇：如何獲得最大狀態作為列？