具有用于计数的多个子查询的SELECT查询 [英] SELECT query with multiple sub-queries for counts

问题描述

我刚刚编写了此报告的查询。但我最初写它没有日期范围过滤器每个子查询。但是没有工作。所以我把它添加到每个子查询。这是有效的，但我不想每次重复它，是否有语法做同样的事情更简单？

  SELECT Count（r.id）AS cnt_total，
（SELECT Count（r1.entity_id）
 FROM auto_reminders_members r1 
 WHERE r1.reminder_id = r.reminder_id 
 AND r1。 date_last_reminder转换（DATETIME，'03 / 28/2013'，
 101）
和
 CONVERT（DATETIME，
 '03 / 28/2013'+'23:59： 59.997'，101）
 AND r1.action ='notnow'）AS cnt_notnow，
（SELECT Count（r1.entity_id）
 FROM auto_reminders_members r1 
 WHERE r1.reminder_id = r .reminder_id 
 AND r1.date_last_reminder转换前（DATETIME，'03 / 28/2013'，
 101）
和
 CONVERT（DATETIME，
 '03 / 28 / 2013'+'23：59：59.997'，101）
 AND r1.action ='insert'）AS cnt_insert，
（SELECT Count（r1.entity_id）
 FROM auto_reminders_members r1 
 WHERE r1.reminder_id = r.reminder_id 
 AND r1.date_last_reminder转换前（DATETIME，'03 / 28/2013'，
 101）
和
 CONVERT（DATETIME ，
 '03 / 28/2013'+'23：59：59.997'，101）
 AND r1.action ='update'）AS cnt_update，
（SELECT Count（r1.entity_id ）
 FROM auto_reminders_members r1 
 WHERE r1.reminder_id = r.reminder_id 
 AND r1.date_last_reminder BETWEEN CONVERT（DATETIME，'03 / 28/2013'，
 101）
 AND 
 CONVERT（DATETIME，
 '03 / 28/2013'+'23：59：59.997'，101）
 AND r1.action ='verify'）AS cnt_verify 
 FROM auto_reminders_members r 
 WHERE r.reminder_id = 1 
 AND r.date_last_reminder转换前（DATETIME，'03 / 28/2013'，101）AND 
 CONVERT（DATETIME，
 '03 / 28/2013'+'23：59：59.997'，101 
）
 GROUP BY r.reminder_id 
  

$

$ b b > SELECT count（r.id）as cnt_total，
sum（case当r.action ='notnow'then 1 else 0 end）as'cnt_notnow'，
sum
sum（case当r.action ='update'then 1 else 0 end）as'cnt_update'，
sum（当r.action ='insert'then 1 else 0 end）as'cnt_insert' （d），（b）（b）（b）（b）（b）（b）（b）（b）（b）（b）（b）（b）（c） r.date_last_reminder）='20130328'


我也清理了一下，因为这总是1，并改变日期比较以避免使用逻辑之间的混乱（感谢Aaron的评论）

I just wrote this query for a report. But I originally wrote it without the date range filter on every sub-query. But that didn't work. So I added it to each sub-query. And that worked, but I don't really like having to repeat it every time, is there syntax to do the same thing simpler?

 SELECT Count(r.id)                       AS cnt_total,
   (SELECT Count(r1.entity_id)
    FROM   auto_reminders_members r1
    WHERE  r1.reminder_id = r.reminder_id
           AND r1.date_last_reminder BETWEEN CONVERT(DATETIME, '03/28/2013',
                                             101)
                                             AND
               CONVERT(DATETIME,
               '03/28/2013' + ' 23:59:59.997 ', 101)
           AND r1.action = 'notnow') AS cnt_notnow,
   (SELECT Count(r1.entity_id)
    FROM   auto_reminders_members r1
    WHERE  r1.reminder_id = r.reminder_id
           AND r1.date_last_reminder BETWEEN CONVERT(DATETIME, '03/28/2013',
                                             101)
                                             AND
               CONVERT(DATETIME,
               '03/28/2013' + ' 23:59:59.997 ', 101)
           AND r1.action = 'insert') AS cnt_insert,
   (SELECT Count(r1.entity_id)
    FROM   auto_reminders_members r1
    WHERE  r1.reminder_id = r.reminder_id
           AND r1.date_last_reminder BETWEEN CONVERT(DATETIME, '03/28/2013',
                                             101)
                                             AND
               CONVERT(DATETIME,
               '03/28/2013' + ' 23:59:59.997 ', 101)
           AND r1.action = 'update') AS cnt_update,
   (SELECT Count(r1.entity_id)
    FROM   auto_reminders_members r1
    WHERE  r1.reminder_id = r.reminder_id
           AND r1.date_last_reminder BETWEEN CONVERT(DATETIME, '03/28/2013',
                                             101)
                                             AND
               CONVERT(DATETIME,
               '03/28/2013' + ' 23:59:59.997 ', 101)
           AND r1.action = 'verify') AS cnt_verify
FROM   auto_reminders_members r
WHERE  r.reminder_id = 1
       AND r.date_last_reminder BETWEEN CONVERT(DATETIME, '03/28/2013', 101) AND
                                            CONVERT(DATETIME,
                                            '03/28/2013' + ' 23:59:59.997 ', 101
                                            )
GROUP  BY r.reminder_id  

解决方案

Here is a way to do that without all the sub-queries

SELECT  Count(r.id) AS cnt_total,
        sum(case when r.action = 'notnow' then 1 else 0 end) as 'cnt_notnow',
        sum(case when r.action = 'insert' then 1 else 0 end) as 'cnt_insert',
        sum(case when r.action = 'update' then 1 else 0 end) as 'cnt_update',
        sum(case when r.action = 'verify' then 1 else 0 end) as 'cnt_verify'        
FROM    auto_reminders_members r

WHERE  r.reminder_id = 1
       AND CONVERT(DATE, r.date_last_reminder) = '20130328'

I also cleaned up the query a bit, removing the group by as this will always be 1, and changing the date comparison to avoid using the messy between logic (Thanks Aaron for the comment)

这篇关于具有用于计数的多个子查询的SELECT查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！