SQL 计数（）/左连接？

SQL COUNT() / LEFT JOIN?

提问by thegunner

I have three tables: calls, attachments and notes and I want to display everything that's in the calls table, but also display whether a call has attachments and whether the call has notes. - by determining if there is an attachment or note record with a call_id in it. There could be notes and attachments, or there may not be but I would need to know.

我有三个表：通话、附件和备注，我想显示通话表中的所有内容，但还要显示通话是否有附件以及通话是否有备注。- 通过确定是否存在带有 call_id 的附件或注释记录。可能有笔记和附件，也可能没有，但我需要知道。

Tables structure:

表结构：

calls:

调用：

call_id  |  title  |  description  

attachments:

附件：

attach_id  |  attach_name  |  call_id  

notes:

笔记：

note_id  |  note_text  |  call_id  

If I write:

如果我写：

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
       , title
       , description

to give me a list of all calls and the number of attachments.

给我一份所有电话和附件数量的清单。

How can I also add in a column with the number of notes or a column which indicates that there is notes?

我如何还可以添加带有注释数量的列或表示有注释的列？

Any ideas?

有任何想法吗？

Thanks.

谢谢。

回答by Martin Smith

For the count

对于计数

SELECT 
     c.call_id, 
     title, 
     description, 
     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,title,description

Or for existence (will be more efficient if this is all you need)

或者为了存在（如果这就是你所需要的，效率会更高）

SELECT 
     c.call_id, 
     title, 
     description, 
     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,title,description

回答by VdesmedT

SELECT c.call_id, title, description, a.call_id, n.call_id
FROM calls c 
LEFT JOIN attachments a ON c.call_id = a.call_id 
LEFT JOIN notes n ON c.call_id = n.call_id
GROUP BY c.call_id,title,description, a.call_id, n.call_id

If call id is present in fiels 4 or 5, you know you have an attachement or a note

如果呼叫 ID 出现在字段 4 或 5 中，则您知道您有附件或便笺

If you need to number of attachement or note, look at other answers, look at AtaTheDev's post.

如果您需要附件或注释的数量，请查看其他答案，查看 AtaTheDev 的帖子。

回答by Robert Koritnik

Use distinctin counts

distinct在计数中使用

You have to use distinct in counts because your groups have grown by two different entities. So you have to only count distinct values of each. This next query will return both counts as well as bitvalues whether there are any attachments and notes.

您必须使用不同的计数，因为您的组由两个不同的实体增长。所以你只需要计算每个的不同值。下一个查询将返回计数和bit值，无论是否有任何附件和注释。

select
    c.call_id, c.title, c.description,
    count(distinct a.attach_id) as attachments_count,
    count(distinct n.note_id) as notes_count,
    /* add these two if you need to */
    case when count(distinct a.attach_id) > 0 then 1 else 0 end as has_attachments,
    case when count(distinct n.note_id) > 0 then 1 else 0 end as has_notes
from calls c
    left join attachments a
    on (a.call_id = c.call_id)
    left join notes n
    on (n.call_id = c.call_id)
group by c.call_id, c.title, c.description

回答by dabal

I think it should be something like this

我认为它应该是这样的

SELECT c.call_id, title, description, count(distinct attach_id) , count(distinct note_id)
FROM calls c
LEFT JOIN attachments a ON c.call_id = a.call_id
LEFT JOIN notes n ON n.call_id = a.call_id
GROUP BY c.call_id,title,description


SELECT c.call_id, title, description, count(distinct attach_id) , count(distinct note_id)
FROM calls c
LEFT JOIN attachments a ON c.call_id = a.call_id
LEFT JOIN notes n ON n.call_id = a.call_id
GROUP BY c.call_id,title,description


回答by Rodolfo Jorge Nemer Nogueira

This also works:

这也有效：

SELECT 
    cl.*,
    (SELECT count(1) FROM attachments AS at WHERE at.call_id = cl.id) as num_attachments,
    (SELECT count(1) FROM notes AS nt WHERE nt.call_id = cl.id) as num_notes,
FROM calls AS cl

回答by garish

I have used this simple query. This query allows you to use main tables columns easily without group by.

我使用了这个简单的查询。此查询允许您轻松使用主表列而无需分组。

   Select StudentName,FatherName,MotherName,DOB,t.count  from Student
   left JOIN
   (
    Select StudentAttendance.StudentID,  count(IsPresent) as count 
    from StudentAttendance
    group by StudentID, IsPresent
   ) as t    
  ON   t.StudentID=Student.StudentID