sql - sql查询获取员工总数和员工人数
问题描述
SELECT person_number,taxable_earn, Basic_Life,Tax_Units,Basic_Spouse,age_depnt,age_EE
FROM (SELECT
pra.result_value ,
pra.elementname,
prd.person_number ,
pap.date_of_birth age_depnt,
pra.date_of_birth age_EE
FROM pay_pay_relationships_dn prd
INNER JOIN pay_payroll_rel_actions pra ON prd.payroll_relationship_id = pra.payroll_relationship_id
INNER JOIN pay_payroll_actions ppa ON pra.payroll_action_id = ppa.payroll_action_id
INNER JOIN pay_all_payrolls_f pap ON ppa.element_type_id = pap.element_type_id
WHERE ppa.payroll_id = pap.payroll_id
AND ppa.effective_date BETWEEN :p_start_date AND :p_end_date)
PIVOT (SUM(result_value )
FOR elementname IN ('taxable_earn' taxable_earn, 'Basic_Life' Basic_Life, 'Tax_Units' Tax_Units,'Basic_Spouse', Basic_Spouse))
上面的查询给了我这样的数据
person_number taxable_earn Basic_Life Tax_Units Basic_Spouse depent_dob age_EE
10 78.9 10.5 39
20 76.7 10.2 12.3 32 21
30 2.3 10.3 8.9 19.0 34 25
40 14.3 8.9 19.0 52 67
我想要以下格式的员工人数和上述每一列的总数 -
Basic_Life taxable_earn Basic_Spouse
Total 34.8 157.9 50.3
EMP_count 3 3 3
我应该为此使用什么功能?
解决方案
WITH pivot_subquery as
(
SELECT person_number,taxable_earn, Basic_Life,Tax_Units,Basic_Spouse,age_depnt,age_EE
FROM (SELECT
pra.result_value ,
pra.elementname,
prd.person_number ,
pap.date_of_birth age_depnt,
pra.date_of_birth age_EE
FROM pay_pay_relationships_dn prd
INNER JOIN pay_payroll_rel_actions pra ON prd.payroll_relationship_id = pra.payroll_relationship_id
INNER JOIN pay_payroll_actions ppa ON pra.payroll_action_id = ppa.payroll_action_id
INNER JOIN pay_all_payrolls_f pap ON ppa.element_type_id = pap.element_type_id
WHERE ppa.payroll_id = pap.payroll_id
AND ppa.effective_date BETWEEN :p_start_date AND :p_end_date)
PIVOT (SUM(result_value )
FOR elementname IN ('taxable_earn' taxable_earn, 'Basic_Life' Basic_Life, 'Tax_Units' Tax_Units,'Basic_Spouse', Basic_Spouse))
)
select 'Total' as row_desc
,sum(nvl(Basic_Life,0.0)) as Basic_Life
,sum(nvl(taxable_earn,0.0)) as taxable_earn
,sum(nvl(Basic_Spouse,0.0)) as Basic_Spouse
from pivot_subquery
union all
select 'EMP_count' as row_desc
,nvl((select count(*) from pivot_subquery where Basic_Life is not null),0) as Basic_Life
,nvl((select count(*) from pivot_subquery where taxable_earnis not null),0) as taxable_earn
,nvl((select count(*) from pivot_subquery where Basic_Spouse is not null),0) as Basic_Spouse
from dual
推荐阅读
- python - 如何在Python中删除两个双括号之间的文本
- clickhouse - Superset 无法访问 ClickHouse 数据库
- javascript - 无法访问从 fetch api 请求中获取结果
- macos - 在 MacOS 上运行 Makefile 时出现部分错误
- powershell - PowerShell 7.0 和 Windows PowerShell 5.1 Get-Member 输出与服务对象
- c++ - 如何在 Eclipse 中正确使用 gmp-mpir-mpfr?
- reactjs - 为什么我在 componentDidMount 中的函数没有被触发?
- linux - 系统重启后重新连接到控制台输出
- python - Sympy:指定函数的导数
- javascript - 使用引导程序显示带有来自 javascript 的两个按钮的确认对话框