使用SQL查询获取特定类别和进一步分支的子类别的计数

本文介绍了使用SQL查询获取特定类别和进一步分支的子类别的计数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我希望使用sql查询从数据库中获取摘要报告，以显示按类别对它们进行分组的计数，还可以进一步显示每个类别下的分组子类别计数. 例如在下面的快照中:我想总结一下我的男性和女性雇员数量数据，还要显示每种性别下不同雇员职位的数量.预先感谢.

I am looking to get a summary report from my database using sql query to show their count grouped them by category but also to further show the grouped subcategory count under each category. e.g. in attached snapshot below: I want to summarize my data for number of male and female employees but also to show under each gender the count of different employee position. Thanks in advance.

推荐答案

假定表结构如下:

id -- primary key gender -- 'Male', 'Female' position -- 'Senior Manager', 'Manager', 'Employee'

您可以UNION ALL一系列聚合查询以产生预期的结果.

You could UNION ALL a series of aggregated queries to produce the expected results.

SELECT cat, cnt FROM ( SELECT 1 rn, 'Male' cat, SUM(gender = 'Male') cnt FROM mytable UNION ALL SELECT 2, 'Senior Manager', SUM(gender = 'Male' and position = 'Senior Manager') FROM mytable UNION ALL SELECT 3, 'Manager', SUM(gender = 'Male' and position = 'Manager') FROM mytable UNION ALL SELECT 4, 'Employee', SUM(gender = 'Male' and position = 'Employee') FROM mytable UNION ALL SELECT 5, 'Female', SUM(gender = 'Female') FROM mytable UNION ALL SELECT 6, 'Senior Manager', SUM(gender = 'Female' and position = 'Senior Manager') FROM mytable UNION ALL SELECT 7, 'Manager', SUM(gender = 'Female' and position = 'Manager') FROM mytable UNION ALL SELECT 8, 'Employee', SUM(gender = 'Female' and position = 'Employee') FROM mytable ) ORDER BY rn

附加列rn用于在结果集中按顺序保留记录.

Additional column rn is there to keep the records in order in the resultset.