104 lines
4.8 KiB
SQL
104 lines
4.8 KiB
SQL
CREATE OR REPLACE VIEW pjcloudx_wallet.v_mch_dept_tree AS
|
|
WITH RECURSIVE
|
|
root AS (SELECT m.dept_id AS root_dept_id,
|
|
m.id AS mch_id,
|
|
m.name AS mch_name
|
|
FROM pjcloudx_wallet.mch_manage AS m
|
|
JOIN pjcloudx.sys_dept AS d
|
|
ON d.dept_id = m.dept_id
|
|
AND d.del_flag = '0'
|
|
WHERE m.del_flag = '0'
|
|
AND m.types = '1'),
|
|
r (dept_id, parent_id, dept_name, root_dept_id, mch_id, mch_name, level) AS (SELECT d.dept_id,
|
|
d.parent_id,
|
|
d.name,
|
|
root.root_dept_id,
|
|
root.mch_id,
|
|
root.mch_name,
|
|
0
|
|
FROM pjcloudx.sys_dept AS d
|
|
JOIN root
|
|
ON root.root_dept_id = d.dept_id
|
|
WHERE d.del_flag = '0'
|
|
UNION ALL
|
|
SELECT c.dept_id,
|
|
c.parent_id,
|
|
c.name,
|
|
r.root_dept_id,
|
|
r.mch_id,
|
|
r.mch_name,
|
|
r.level + 1
|
|
FROM pjcloudx.sys_dept AS c
|
|
JOIN r
|
|
ON c.parent_id = r.dept_id
|
|
WHERE c.del_flag = '0')
|
|
SELECT root_dept_id AS mchDeptId,
|
|
mch_id AS mchId,
|
|
mch_name AS mchName,
|
|
dept_id AS deptId,
|
|
parent_id AS parentId,
|
|
dept_name AS deptName,
|
|
level AS level
|
|
FROM r
|
|
ORDER BY mchDeptId, level, deptId;
|
|
|
|
|
|
|
|
|
|
CREATE OR REPLACE VIEW pjcloudx_wallet.v_all_mch_dept_tree AS
|
|
WITH RECURSIVE
|
|
root AS (SELECT m.types AS mch_type,
|
|
m.dept_id AS root_dept_id,
|
|
m.id AS mch_id,
|
|
m.name AS mch_name
|
|
FROM pjcloudx_wallet.mch_manage AS m
|
|
JOIN pjcloudx.sys_dept AS d
|
|
ON d.dept_id = m.dept_id
|
|
AND d.del_flag = '0'
|
|
WHERE m.del_flag = '0'),
|
|
r (
|
|
mch_type,
|
|
dept_id,
|
|
parent_id,
|
|
dept_name,
|
|
root_dept_id,
|
|
mch_id,
|
|
mch_name,
|
|
level
|
|
) AS (SELECT root.mch_type,
|
|
d.dept_id,
|
|
d.parent_id,
|
|
d.name,
|
|
root.root_dept_id,
|
|
root.mch_id,
|
|
root.mch_name,
|
|
0
|
|
FROM pjcloudx.sys_dept AS d
|
|
JOIN root
|
|
ON root.root_dept_id = d.dept_id
|
|
WHERE d.del_flag = '0'
|
|
|
|
UNION ALL
|
|
|
|
SELECT r.mch_type,
|
|
c.dept_id,
|
|
c.parent_id,
|
|
c.name,
|
|
r.root_dept_id,
|
|
r.mch_id,
|
|
r.mch_name,
|
|
r.level + 1
|
|
FROM pjcloudx.sys_dept AS c
|
|
JOIN r
|
|
ON c.parent_id = r.dept_id
|
|
WHERE c.del_flag = '0')
|
|
SELECT mch_type AS mchType,
|
|
root_dept_id AS mchDeptId,
|
|
mch_id AS mchId,
|
|
mch_name AS mchName,
|
|
dept_id AS deptId,
|
|
parent_id AS parentId,
|
|
dept_name AS deptName,
|
|
level AS level
|
|
FROM r
|
|
ORDER BY mchType, mchDeptId, level, deptId; |