mysql - 在不创建其他 View 的情况下计算 SQL 语句

Question

我有一个名为 JOB_Details 的表，数据显示如下:

 Employee_ID   Age          Department     Gender       
    001       30yrs          IT               M 
    002       34yrs          HR               F    
    003       39yrs          HR               F   
    004       49yrs          Finance          M        
    005       54yrs          IT               M 
    006       20yrs          HR               M 
    007       24yrs          HR               F   
    008       33yrs          Finance          F       
    009       29yrs          Finance          F        
    010       44yrs          IT               M

我希望显示的输出应该是这样的

Age          Department:IT       Department:Finance  Department:HR    Total
             Male     Female     Male     Female     Male     Female
<30yrs         1         0         0         1         1         1       4
 30-34yrs      0         0         0         1         0         1       2
 35-39yrs      0         0         0         0         0         1       1
 40-49yrs      1         0         1         0         0         0       2
 50-54yrs      1         0         0         0         0         0       1
 Total         3         0         1         2         1         3       10 

据我所知，唯一的方法是创建另一个 View 并将它们一个接一个地分组。但我想知道在不创建其他 View 的情况下还有其他方法吗？如果有人有其他好的建议，我希望学习它。非常感谢。

Answer 1

MySQL 版本。在 SQL Server 和 Oracle 中都不起作用。 SQL Fiddle

select *, IT_Male + IT_Female + Finance_Male + Finance_Female + HR_Male + HR_Female as Total
from (
    select 
        ar.`range` as Age,
        count(Department = 'IT' and Gender = 'M' or null) as IT_Male,
        count(Department = 'IT' and Gender = 'F' or null) as IT_Female,
        count(Department = 'Finance' and Gender = 'M' or null) as Finance_Male,
        count(Department = 'Finance' and Gender = 'F' or null) as Finance_Female,
        count(Department = 'HR' and Gender = 'M' or null) as HR_Male,
        count(Department = 'HR' and Gender = 'F' or null) as HR_Female
    from 
        JOB_Details jd
        inner join
        age_range ar on jd.Age between ar.bottom and ar.top
    group by ar.`range`
    order by ar.bottom
) s
union
select 
    'Total',
    count(Department = 'IT' and Gender = 'M' or null),
    count(Department = 'IT' and Gender = 'F' or null),
    count(Department = 'Finance' and Gender = 'M' or null),
    count(Department = 'Finance' and Gender = 'F' or null),
    count(Department = 'HR' and Gender = 'M' or null),
    count(Department = 'HR' and Gender = 'F' or null),
    count(*)
from JOB_Details

没有外部表或 View 的 Oracle 版本:SQL Fiddle

with age_range as (
    select 0 as "bottom", 29 as "top", '<30' as "range" from dual union
    select 30, 34, '30-34' from dual union
    select 35, 39, '35-59' from dual union
    select 40, 49, '40-49' from dual union
    select 50, 54, '50-54' from dual
)
select s.*, IT_Male + IT_Female + Finance_Male + Finance_Female + HR_Male + HR_Female as Total
from (
    select 
        ar."range" as Age,
        count(case when Department = 'IT' and Gender = 'M' then 1 end) as IT_Male,
        count(case when Department = 'IT' and Gender = 'F' then 1 end) as IT_Female,
        count(case when Department = 'Finance' and Gender = 'M' then 1 end) as Finance_Male,
        count(case when Department = 'Finance' and Gender = 'F' then 1 end) as Finance_Female,
        count(case when Department = 'HR' and Gender = 'M' then 1 end) as HR_Male,
        count(case when Department = 'HR' and Gender = 'F' then 1 end) as HR_Female
    from 
        JOB_Details jd
        inner join
        age_range ar on jd.Age between ar."bottom" and ar."top"
    group by ar."range", ar."bottom"
    order by ar."bottom"
) s
union
select 
    'Total',
    count(case when Department = 'IT' and Gender = 'M' then 1 end),
    count(case when Department = 'IT' and Gender = 'F' then 1 end),
    count(case when Department = 'Finance' and Gender = 'M' then 1 end),
    count(case when Department = 'Finance' and Gender = 'F' then 1 end),
    count(case when Department = 'HR' and Gender = 'M' then 1 end),
    count(case when Department = 'HR' and Gender = 'F' then 1 end),
    count(*)
from JOB_Details