SQL COUNT() / LEFT JOIN?

Question

我有三个表：calls，attachments和notes。我想显示calls表中的所有内容，并显示呼叫是否有附件和笔记-通过确定其中是否存在具有call_id的附件或笔记记录来实现。可能有笔记和附件，也可能没有，但我需要知道。

表结构：

calls：

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

给我一个列表，其中包括所有电话的呼叫和附件数量。

我如何添加一列显示注释的数量或指示存在注释的列？

有什么想法吗？

谢谢。

- thegunner

Answer 1

计数

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

或者对于存在性检查（如果这是您所需的全部内容，这种方法会更有效）

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

- Martin Smith

---

你只选择主键而不是所有列 * 在 exists() 语句中，速度会更快，不是吗？ - Robert Koritnik

@Robert - 不是的，那是一个谬论。 - Martin Smith

@Martin：但这是一种非常好的实践，特别是当你在客户端使用EF时，它确实会产生巨大的影响。 - Robert Koritnik

嘿，大家都回答得很好。这个是最先发布的，我会选择你高效的方案Martin - 但我会像Robert提到的那样使用主键。谢谢大家。 - thegunner

哦，还必须将其更改为“WHERE n.call_id = c.call_id”。 - thegunner

Answer 2

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

如果字段4或5中存在呼叫ID，则表示您有一个附件或注释。

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

- VdesmedT

Answer 3

在计数中使用distinct

由于您的组已经增加了两个不同的实体，因此您必须在计数中使用distinct，所以您只需要计算每个不同值。下一个查询将返回两个计数以及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

- Robert Koritnik

---

投票反对的人应该提供评论。我不明白为什么我的答案不正确？ - Robert Koritnik

Answer 4

我使用了这个简单的查询。这个查询允许你轻松地使用主表列而不需要分组。

   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

- garish

Answer 5

我认为应该是这样的：

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


该代码段涉及编程相关内容。

- dabal

Answer 6

这个也可以：

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

- Rodolfo Jorge Nemer Nogueira