SQL COUNT()/ LEFT JOIN?

栏目: mysql · 发布时间: 2021-05-03

简介  这篇文章主要介绍了SQL COUNT()/ LEFT JOIN?以及相关的经验技巧,文章约3659字,浏览量406,点赞数6,值得推荐!

我有三个表:呼叫,附件和备注,我想显示呼叫表中的所有内容,还显示呼叫是否有附件以及呼叫是否有备注。 - 通过确定是否存在带有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
       , title
       , description

给我一个所有电话和附件数量的列表。

如何添加包含注释数量的列或指示有注释的列?

有任何想法吗?

谢谢。

答案

为了伯爵

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
另一答案
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的帖子。

另一答案

我认为它应该是这样的

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

另一答案

在计数中使用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
另一答案

这也有效:

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
另一答案

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

   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

以上就是本文的全部内容,希望对大家的学习有所帮助,本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。

SQL表连接查询(inner join、full join、left join、right join)(示例代码)

INNER JOIN与LEFT JOIN在SQL Server的性能

sql之left join、right join、inner join的区别

sql之left join、right join、inner join的区别

sql之left join、right join、inner join的区别

SQL的连接(join)有哪些常见形式?解释一下?解释 SQL 的 left join 和 right join?

sql的left join 、right join 、inner join之间的区别

sql语句中 left join,right join,inner join 的区别