MySQL - 添加总计行和列的最简单方法

Question

我编写的报告包括总计列和总计行。到目前为止，我只是使用子查询分配变量并将它们添加到总计列中。对于总计行，我刚刚执行了联合并重新运行查询，但约束更少。这是代码。

set @sdate = '2015-01-01';
set @edate = @sdate + interval 1 year;

select `a` as 'First Name', `aa` as 'Last Name',
    `b` as 'Field Tests', `c` as 'Field Inspections',
    `d` as 'Lab Tests', `e` as 'Lab Inspections',
    `f` as 'Total' from
(
    select a.first as 'a', a.last as 'aa',
    (select @b := count(*) from field_test b where b.inspector_id = a.id
        and b.date_time >= @sdate and b.date_time < @edate) as 'b',
    (select @c := count(*) from field_insp c where c.inspector_id = a.id
        and c.inspection_date >= @sdate and c.inspection_date < @edate) as 'c',
    (select @d := count(*) from lab_test d where d.inspector_id = a.id
        and d.date_time >= @sdate and d.date_time < @edate) as 'd',
    (select @e := count(*) from lab_insp e where e.inspector_id = a.id
        and e.inspection_date >= @sdate and e.inspection_date < @edate) as 'e',
    (select @f := @b + @c + @d + @e) as 'f'
    from inspector a
    order by `f` desc
) as t
where `f` > 0
union
select 'TOTALS', '',
    (select @n := count(*) from field_test
        where date_time >= @sdate and date_time < @edate),
    (select @o := count(*) from field_insp
        where inspection_date >= @sdate and inspection_date < @edate),
    (select @p := count(*) from lab_test
        where date_time >= @sdate and date_time < @edate),
    (select @q := count(*) from lab_insp
        where inspection_date >= @sdate and inspection_date < @edate),
    (select @n + @o + @p + @q)

有点冗长和困惑，但它生成了一份不错的报告。我最初用连接编写它，但我用子查询重写了它，因为当时它似乎是添加总计列和总计行的最简单方法。除了代码的冗长之外，在使用较大表的类似报告上运行所有这些子查询所花费的时间有点长。

Answer 1

您可以使用 group by 子句的 with rollup 修饰符。为此，您需要按检查员分组。

这是一种方法:

select     case when id is null then 'Totals' else min(first) end as `First name`, 
           case when id is null then ''       else min(last)  end as `Last Name`,
           sum(ft) as `Field Tests`,
           sum(fi) as `Field Inspections`,
           sum(lt) as `Lab Tests`,
           sum(li) as `Lab Inspections`,
           sum(ft + fi + lt + li) as `Total`
from       inspector as insp
inner join (
            select   inspector_id, count(*) as ft, 0 as fi, 0 as lt, 0 as li 
            from     field_test
            where    date_time >= @sdate and date_time < @edate
            group by inspector_id
            union
            select   inspector_id, 0, count(*), 0, 0
            from     field_insp 
            where    inspection_date >= @sdate and inspection_date < @edate
            group by inspector_id
            union
            select   inspector_id, 0, 0, count(*), 0
            from     lab_test 
            where    date_time >= @sdate and date_time < @edate
            group by inspector_id
            union
            select   inspector_id, 0, 0, 0, count(*)
            from     lab_insp
            where    inspection_date >= @sdate and inspection_date < @edate
            group by inspector_id
           ) as cnt
         on cnt.inspector_id = id
group by   id with rollup;