在子查询中使用汇总

Question

这个查询针对同一个表(qcheck)运行多个计数，给出每个工程师所做测试的总数，这些测试被细分为小时、日、周、月。我还包括一个屏幕截图的结果和数据。

我想要汇总，这样我就可以得到总数，但是在使用子查询时不确定如何做，因为我可以将总数加入到任何东西中。

数据图片

结果图

代码：

select coalesce(main.checkby, 'Total') as checkby_or_total,
       lfaulty,
       lfully,
       ltotal,
       dfaulty,
       dfully,
       dtotal,
       wfaulty,
       wfully,
       wtotal,
       mfaulty,
       mfully,
       mtotal
from   (
        select   qcheck.checkby,
                 count(case result when 'fully tested & working' then 1 end)     as mfully,
                 count(case result when 'faulty' then 1 end)                     as mfaulty,
                 count(*) as mtotal
        from     qcheck
        where    YEAR(finishdate) = YEAR(CURDATE()) AND MONTH(finishdate) = MONTH(CURDATE())
        and      qcheck.checkby not like 'michael'
        and      qcheck.checkby not like 'chaz'
        group by qcheck.checkby with rollup
        ) as main
Left join ( select   qcheck.checkby,
                 count(case result when 'fully tested & working' then 1 end)     as dfully,
                 count(case result when 'faulty' then 1 end)                     as dfaulty,
                 count(*) as dtotal
        from     qcheck
        where    finishdate >= now()-interval 12 hour
        and      qcheck.checkby not like 'michael'
        and      qcheck.checkby not like 'chaz'
        group by qcheck.checkby with rollup) as today on today.checkby =main.checkby
Left join ( select   qcheck.checkby,
                 count(case result when 'fully tested & working' then 1 end)     as wfully,
                 count(case result when 'faulty' then 1 end)                     as wfaulty,
                 count(*) as wtotal
        from     qcheck
        where    YEARWEEK(finishdate)=YEARWEEK(NOW())
        and      qcheck.checkby not like 'michael'
        and      qcheck.checkby not like 'chaz'
        group by qcheck.checkby with rollup) as week on week.checkby =main.checkby
Left join ( select   qcheck.checkby,
                 count(case result when 'fully tested & working' then 1 end)     as lfully,
                 count(case result when 'faulty' then 1 end)                     as lfaulty,
                 count(*) as ltotal
        from     qcheck
        where    finishdate >= now()-interval 1 hour
        and      qcheck.checkby not like 'michael'
        and      qcheck.checkby not like 'chaz'
        group by qcheck.checkby with rollup) as month on month.checkby =main.checkby
order by    main.checkby is null,
            mtotal desc

Answer 1

子查询中产生的汇总行由于联接条件xxxx.checkby =main.checkby而丢失。如果任何一方都是false，则此条件为null。特别是：null = null是false。

此外，你可能会有一些边境案件，你会有超过两个月的一周，所以每周的计数可能超过当前月份的计数。这本身并不是一个问题，但是join链从每月计数开始的方式，您可能会错过每周计数中的一些记录。即当月还没有记录的人的每周记录，将不会在本周统计中得到统计。

您获得的null值是外部(left)联接的结果。当外部联接条件为false时，所有字段(以及计数)都将在引用时生成null。

以上所有问题都可以通过执行一个大的子查询来解决，使用更详细的case when条件而不是where条件：

select    *
from      (
          select   coalesce(checkby, 'Total') as checkby_or_total,
                   count(case when last_day(finishdate) = last_day(curdate())
                               and result = 'fully tested & working' 
                         then 1 end) as mfully,
                   count(case when last_day(finishdate) = last_day(curdate())
                               and result = 'faulty' 
                         then 1 end) as mfaulty,
                   count(case when last_day(finishdate) = last_day(curdate())
                         then 1 end) as mtotal,
                   count(case when date(finishdate) = curdate()
                               and result = 'fully tested & working' 
                         then 1 end) as dfully,
                   count(case when date(finishdate) = curdate()
                               and result = 'faulty'
                         then 1 end) as dfaulty,
                   count(case when date(finishdate) = curdate()
                         then 1 end) as dtotal,
                   count(case when yearweek(finishdate) = yearweek(curdate())
                               and result = 'fully tested & working' 
                         then 1 end) as wfully,
                   count(case when yearweek(finishdate) = yearweek(curdate())
                               and result = 'faulty'
                         then 1 end) as wfaulty,
                   count(case when yearweek(finishdate) = yearweek(curdate())
                         then 1 end) as wtotal,
                   count(case when finishdate >= now() - interval 1 hour
                               and result = 'fully tested & working' 
                         then 1 end) as lfully,
                   count(case when finishdate >= now() - interval 1 hour
                               and result = 'faulty'
                         then 1 end) as lfaulty,
                   count(case when finishdate >= now() - interval 1 hour
                         then 1 end) as ltotal
          from     qcheck
          where    checkby not in ('michael', 'chaz')
          group by checkby with rollup) as main
order by  checkby_or_total = 'Total',
          mtotal desc

我还介绍了一些不同的条件：

• 使用last_day(finishdate) = last_day(curdate())可以更有效地创建月份条件。
• 同一天的条件可能是date(finishdate) = curdate()，尽管这与您使用的条件不完全相同。但也许你想考虑一下这个。