根据上级部门查本级及下级的sql

阿虎-cqh

于 2023-12-06 16:03:24 发布

阅读量136

点赞数

文章标签： sql 数据库前端

本文链接：https://blog.csdn.net/chenqinghu98/article/details/134834165

版权

select 
aa.dept_id, aa.dept_name,c.dept_name as parentName,
sum(bb.predictCharge) as predictCharge,
sum(bb.actualCharge) as actualCharge,
sum(bb.countyAllot) as countyAllot,
sum(bb.townAllot) as townAllot,
sum(bb.villageAllot) as villageAllot
from sys_dept aa 
left join ( 
SELECT 
b.dept_id, d.ancestors,d.dept_name,
 round( sum( bill_amount ), 2 ) AS predictCharge,
	round( sum( IF ( trade_status = '2', bill_amount, 0 ) ), 2 ) AS actualCharge,
round (sum( IF ( trade_status = '2', bill_amount, 0 ) )-FLOOR( sum( IF ( trade_status = '2', bill_amount, 0 ) ) * 0.2 ) - FLOOR( sum( IF ( trade_status = '2', bill_amount, 0 ) ) * 0.3 ),2) AS countyAllot, 
	FLOOR( sum( IF ( trade_status = '2', bill_amount, 0 ) ) * 0.2 ) AS townAllot, 
	FLOOR( sum( IF ( trade_status = '2', bill_amount, 0 ) ) * 0.3 )  as villageAllot 
FROM nyys_bill b 
JOIN sys_dept d ON b.dept_id = d.dept_id where b.del_flag = '0' AND bill_year = 2023 group by dept_id  
) bb on aa.dept_id = bb.dept_id or FIND_IN_SET(aa.dept_id, bb.ancestors) 
LEFT JOIN sys_dept c on aa.parent_id=c.dept_id 
where aa.parent_id =100
group by aa.dept_id 
 ORDER BY  aa.dept_id