##查询所有上级
with cte_organ(organ_id, organ_pre_id, organ_no, organ_name, organ_type, organ_level, proj_id, enterprise_id, order_id,organ_relation,source_organ_id)
as
(
select organ_id, organ_pre_id, organ_no, organ_name, organ_type, organ_level, proj_id, enterprise_id, order_id,organ_relation,source_organ_id
from d_sys_organ
where organ_id = 73 and deleted_flag = 0
union all
select a.organ_id, a.organ_pre_id, a.organ_no, a.organ_name, a.organ_type, a.organ_level, a.proj_id, a.enterprise_id, a.order_id,a.organ_relation,a.source_organ_id
from d_sys_organ a
inner join
cte_organ b
on a.organ_id=b.organ_pre_id where a.deleted_flag = 0
)
select organ_id, organ_pre_id, organ_no, organ_name, organ_type, organ_level, proj_id, enterprise_id, order_id,organ_relation,source_organ_id from cte_organ order by organ_id
with test_student(stu_id, stu_name)
as
(
select stu_id, stu_name
from sys_student
where school_id = '1' and del_flag = 0
union all
select a.parent_id, a.stu_id, a.stu_name
from sys_student a
inner join
test_student b
on a.stu_id=b.parent_id where a.del_flag = 0
)
select stu_id, stu_name from test_student order by stu_id
##查询所有下级
with cte_organ(organ_id, organ_pre_id, organ_no, organ_name, organ_type, organ_level, proj_id, enterprise_id, order_id,organ_relation,source_organ_id)
as
(
select organ_id, organ_pre_id, organ_no, organ_name, organ_type, organ_level, proj_id, enterprise_id, order_id,organ_relation,source_organ_id
from d_sys_organ
where organ_id = 3 and deleted_flag = 0
union all
select a.organ_id, a.organ_pre_id, a.organ_no, a.organ_name, a.organ_type, a.organ_level, a.proj_id, a.enterprise_id, a.order_id,a.organ_relation,a.source_organ_id
from d_sys_organ a
inner join
cte_organ b
on a.organ_pre_id=b.organ_id where a.deleted_flag = 0
)
select organ_id, organ_pre_id, organ_no, organ_name, organ_type, organ_level, proj_id, enterprise_id, order_id,organ_relation,source_organ_id from cte_organ order by organ_id
WITH cte_jlgl(periodId, periodName, thisSum, zhtbl)
AS(
SELECT pd.PERIOD_ID AS periodId,pd.PERIOD_NAME AS periodName, ISNULL((zf.this_sum)/10000, 0) AS thisSum,
(ISNULL(zf.this_sum, 0))/(SELECT dbo.fnzero(ISNULL(htzj,0)) FROM d_ht_htgk WHERE organ_id = #{organId}) AS zhtbl
FROM d_ht_period pd
LEFT JOIN d_gcjl_hz_zqzf zf ON pd.ORGAN_ID = zf.organ_id AND pd.PERIOD_ID = zf.period_id AND zf.item_id = 230 AND zf.data_mark = 9
WHERE pd.ORGAN_ID = #{organId}
)
SELECT periodId, periodName,
CASE dbo.fnzero(ISNULL(thisSum, 0)) WHEN '' THEN 0 WHEN NULL THEN 0 ELSE CONVERT(DECIMAL(20, 2), dbo.fnzero(ISNULL(thisSum, 0))) END AS thisSum,
CASE dbo.fnzero(ISNULL(zhtbl, 0)) WHEN '' THEN 0 WHEN NULL THEN 0 ELSE CONVERT(DECIMAL(20, 2), dbo.fnzero(ISNULL(zhtbl, 0))) END AS zhtbl
FROM cte_jlgl
UNION ALL
SELECT NULL AS periodId, '合计' AS periodName, SUM(CONVERT(DECIMAL(20, 2), thisSum)) AS thisSum, SUM(CONVERT(DECIMAL(20, 2), zhtbl)) AS zhtbl FROM cte_jlgl
ORDER BY periodId ASC