我有三个表：呼叫,附件和备注,我想显示呼叫表中的所有内容,还显示呼叫是否有附件以及呼叫是否有备注. – 通过确定是否存在带有call_id的附件或注释记录.可能有笔记和附件,或者可能没有,但我需要知道.
表结构：
要求：
call_id | title | description
附件：
attach_id | attach_name | call_id
笔记：
note_id | note_text | call_id
如果我写：
SELECT c.call_id,title,description,count(attach_id) FROM calls c LEFT JOIN attachments a ON c.call_id = a.call_id GROUP BY c.call_id,description
给我一个所有电话和附件数量的列表.
有任何想法吗？
谢谢.
解决方法
为了计数
SELECT
c.call_id,count(DISTINCT attach_id) AS attachment_count,count(DISTINCT note_id) AS notes_count
FROM calls c
LEFT JOIN attachments a ON c.call_id = a.call_id
LEFT JOIN notes n ON n.call_id = c.call_id
GROUP BY c.call_id,description
或者存在(如果这是你需要的话会更有效率)
SELECT
c.call_id,count(attach_id) AS attachment_count,case
when exists (select * from notes n WHERE n.call_id = c.call_id) then
cast(1 as bit)
else
cast(0 as bit)
end as notes_exist
FROM calls c
LEFT JOIN attachments a ON c.call_id = a.call_id
GROUP BY c.call_id,description
