我想生成报告，需要对数据应用6-8个筛选器

Question

我必须按年龄分组，需要在奖金，性别，is_permanent和然后部门的基础上显示结果。这类报告的sql查询是什么？

部门-永久-性别-月度奖金-季度奖金-年度奖金

所有数据都应该来自EMPLOYEE表，如下所示

empid| DOB     | gender| annual_bonus| monthly_bonus| quarterly_bonus| Is_Permanent| Department|
-------------------------------------------------------------------------------------------
101  | 31122002| M     | 100        | 300          | 600            | No           | Admin|
102  | 31122002| f     | 120        | 3020         | 600            | No           | Admin|
103  | 31122001| M     | 1230       | 300          | 6100           | No           | Sales|
104  | 31122001| f     | 1100       | 3200         | 1600           | No           | Sales|
105  | 31122000| M     | 1100       | 300          | 6010           | No           | Marketing|
106  | 31122000| f     | 1200       | 300          | 600            | No           | Marketing|
107  | 31121999| M     | 1100       | 300          | 600            | No           | Finance|
108  | 31121999| f     | 1200       | 300          | 600            | No           | Finance|
109  | 31121998| M     | 1200       | 300          | 600            | No           | Purchase|
110  | 31121998| f     | 1200       | 300          | 600            | No           | Purchase|
111  | 31121997| M     | 1200       | 300          | 600            | No           | Transport|
112  | 31121997| f     | 1200       | 300          | 600            | No           | Transport|

Answer 1

您需要按如下方式使用aggregate function：

With dataa as
(Select trunc(months_between(sysdate, dob)/12) as age, 
       sum(annual_bonus) as  annual_bonus,
       sum(monthly_bonus) as monthly_bonus,
       sum(quarterly_bonus) as quarterly_bonus,
       Is_permemant
  From employee 
 Where gender = 'M'
 Group by trunc(months_between(sysdate, dob)/12), Is_permemant )
Select coalesce(t1.age, t2.age) as age, 
       t1.annual_bonus, t1.monthly_bonus,t1.quarterly_bonus,
       t2.annual_bonus, t2.monthly_bonus,t2.quarterly_bonus
From dataa t1 full outer join dataa t2
  On t1.age = t2.age
 And t1.Is_permemant = 'Yes' and t2.Is_permemant = 'No'

干杯！！

Answer 2

您似乎想要条件聚合。我建议将数据放在行中，并在应用程序中旋转：

select department, is_permenanent, gender,
       floor(months_between(dob, sysdate) / 12) as age, 
       sum(annual_bonus),
       sum(monthly_bonus)
       sum(quarterly_bonus)
from employee
group by department, is_permenanent, gender, 
         floor(months_between(dob, sysdate) / 12);

您可以使用条件聚合在SQL中透视数据：

select floor(months_between(dob, sysdate) / 12) as age,
       sum(case when department = 'Sales' and
                     is_permanent = 'Yes' and
                     gender = 'F'
                then annual_bonus else 0
            end) as sales_perm_f_annual,
       sum(case when department = 'Sales' and
                     is_permanent = 'Yes' and
                     gender = 'F'
                then annual_monthly else 0
            end) as sales_perm_f_monthly,
       sum(case when department = 'Sales' and
                     is_permanent = 'Yes' and
                     gender = 'F'
                then quarterly_bonus else 0
            end) as sales_perm_f_quarterly_bonus,
        . . . 
from employee
group by floor(months_between(dob, sysdate) / 12);