56,673
社区成员
发帖
与我相关
我的任务
分享
--测试数据
with 部门表a (depid,depname)
as
(
select 1,'研发部' union all
SELECT 2,'产品部'
),部门员工表b(usersid,depid)AS(
select 1,1 union all
select 2,2 union all
select 3,1 union all
select 4,1 union all
select 5,2
),员工工资表c(upid,usersid,payment,type)AS(
select 1,1,100,1 union all
select 2,2,300,2 union all
select 3,3,500,2 union all
select 4,4,600,1 union all
select 5,5,800,1
)
--测试数据结束
SELECT a.depname AS 部门名称 ,
COUNT(DISTINCT b.usersid) AS 部门员工数 ,
SUM(CASE WHEN c.type = 1 THEN c.payment
ELSE 0
END) AS 部门RMB工资总额 ,
SUM(CASE WHEN c.type = 2 THEN c.payment
ELSE 0
END) AS 部门美元工资总额
FROM 部门表a a
INNER JOIN 部门员工表b b ON b.depid = a.depid
INNER JOIN 员工工资表c c ON c.usersid = b.usersid
GROUP BY a.depname