mysql - 如何使用 group by 子句对数据透视表 (MySQL) 中的列值进行小计

Question

我有一个包含相应字段的数据透视表。

1. 区
2. 阻止
3. 男性计数
4. 女性计数

以上是列名。因此，基本上，我已按地区和街区对所有这些文件进行了分组。为此，我在 MySQL 中编写了以下查询。

select 
ds.ds_name as district,
bs.bl_name as block,

case
when sd.gender_id = 1 then count(gender_id)
else 0 
end as MaleCount,

case
when sd.gender_id = then count(gender_id)
else 0
end as FemalCount,

from studet data sd
inner join district ds on ds.district_id = sd.ds_id

inner join block bs on bs.block_id = sd.bs_id

group by bs.block_name, ds.district_name;

给出以下结果

    district | block | FemalCount | MaleCount

    xyz      | abc   | 4          | 5

             | cvz   | 5          | 9

    ytz      | tyz   | 7          | 3

             | awe   |3           |8

最主要的是，我想要以下类型的结果集。

   district | block | FemalCount | MaleCount

    xyz      | abc   | 4          | 5

             | cvz   | 5          | 9

    total    |       | 9          | 14

    ytz      | tyz   | 7          | 3

             | awe   | 3          | 8

    total    |       | 10         | 11

其中包括 Malecount 和 FemaleCount 的小计行，以及如上所述的按街区和地区分组。我怎样才能在 MySQL 查询中实现这一点？

Answer 1

您可以添加一个并集(使用 block 名称的高值将其推到排序依据的末尾)来计算给定的按地区的总数

+----------+-----------+-------+-------+
| sudentid | gender_id | ds_id | bs_id |
+----------+-----------+-------+-------+
|      101 |         2 |     3 |     2 |
|      103 |         2 |     3 |     2 |
|      112 |         1 |     3 |     3 |
|      116 |         1 |     3 |     3 |
|      117 |         1 |     3 |     3 |
|        1 |         1 |     1 |     1 |
|        2 |         1 |     1 |     1 |
|        3 |         1 |     1 |     1 |
+----------+-----------+-------+-------+
8 rows in set (0.00 sec)

DROP TABLE IF EXISTS DISTRICT,BLOCK;

CREATE TABLE DISTRICT (ID INT, name varchar(10));
create table block(id int, name varchar(10));

insert into district values (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd');

insert into block values (1,'E'),(2,'F'),(3,'G');

select 
         case when block = 'zzz' then 'Total'
                else    district
         end as district,
         case when block = 'zzz' then ''
         else block
         end as block, 
         malecount,femalecount
from
(
select 
ds.name as district,
bs.name as block,
sum(case when gender_id = 1 then 1 else 0 end) Malecount,
sum(case when gender_id = 2 then 1 else 0 end) Femalecount
from student sd
inner join district ds on ds.id = sd.ds_id
inner join block bs on bs.id = sd.bs_id
group by ds.name, bs.name

union all
select district,'zzz', malecount,femalecount
from
(
select ds.name as district,
sum(case when gender_id = 1 then 1 else 0 end) Malecount,
sum(case when gender_id = 2 then 1 else 0 end) Femalecount
from student sd
inner join district ds on ds.id = sd.ds_id
group by ds.name
) s
) t
order by t.district,t.block
;

结果

+----------+-------+-----------+-------------+
| district | block | malecount | femalecount |
+----------+-------+-----------+-------------+
| aaa      | E     |         3 |           0 |
| Total    |       |         3 |           0 |
| ccc      | F     |         0 |           2 |
| ccc      | G     |         3 |           0 |
| Total    |       |         3 |           2 |
+----------+-------+-----------+-------------+
5 rows in set (0.00 sec)

注意稍微不那么冗长的条件聚合(sum(case when...)