WITH dept AS ( SELECT '1' AS DEPT_ID,'' AS PARENT_ID,'山東1' AS DEPT_NAME FROM DUAL UNION ALL SELECT '2' AS DEPT_ID,'1' AS PARENT_ID,'山東1-2' AS DEPT_NAME FROM DUAL UNION ALL SELECT '3' AS DEPT_ID,'1' AS PARENT_ID,'山東1-3' AS DEPT_NAME FROM DUAL UNION ALL SELECT '4' AS DEPT_ID,'2' AS PARENT_ID,'山東1-2-4' AS DEPT_NAME FROM DUAL UNION ALL SELECT '5' AS DEPT_ID,'2' AS PARENT_ID,'山東1-2-5' AS DEPT_NAME FROM DUAL UNION ALL SELECT '6' AS DEPT_ID,'3' AS PARENT_ID,'山東1-3-6' AS DEPT_NAME FROM DUAL UNION ALL SELECT '7' AS DEPT_ID,'3' AS PARENT_ID,'山東1-3-7' AS DEPT_NAME FROM DUAL UNION ALL SELECT '8' AS DEPT_ID,'3' AS PARENT_ID,'山東1-3-8' AS DEPT_NAME FROM DUAL UNION ALL SELECT '9' AS DEPT_ID,'' AS PARENT_ID,'山西1' AS DEPT_NAME FROM DUAL UNION ALL SELECT '10' AS DEPT_ID,'9' AS PARENT_ID,'山西1-2' AS DEPT_NAME FROM DUAL ),product AS ( SELECT '1' AS PRODUCT_ID,'6' AS DEPT_ID,'産品1' AS NAME FROM DUAL UNION ALL SELECT '2' AS PRODUCT_ID,'7' AS DEPT_ID,'産品2' AS NAME FROM DUAL UNION ALL SELECT '3' AS PRODUCT_ID,'8' AS DEPT_ID,'産品3' AS NAME FROM DUAL UNION ALL SELECT '4' AS PRODUCT_ID,'2' AS DEPT_ID,'産品4' AS NAME FROM DUAL UNION ALL SELECT '5' AS PRODUCT_ID,'10' AS DEPT_ID,'産品5' AS NAME FROM DUAL ) select tb.*,ltrim(SYS_CONNECT_BY_PATH(DEPT_NAME, '==>'), '==>') as str from (select dept.DEPT_ID,PARENT_ID,DEPT_NAME,NAME from dept left join product on dept.DEPT_ID = product.DEPT_ID) tb start with PARENT_ID is null connect by prior DEPT_ID = PARENT_ID
楼主发的问题非常好,把表结构以及数据都写出来,省去了很多的麻烦。 我的方法比较笨,好在结果是符合你的要求的。你看看,有问题再说 SELECT CASE WHEN INSTR(K.RN, '-', -1, 2) > 0 THEN SUBSTR(K.RN, 1, INSTR(K.RN, '-', -1, 2) - 1) ELSE NULL END DEPT_NAME, CASE WHEN INSTR(K.RN, '-', -1, 1) > 0 THEN SUBSTR(K.RN, 1, INSTR(K.RN, '-', -1, 1) - 1) ELSE NULL END DEPT_NAME, K.RN DEPT_NAME, K.PRODUCT_ID, K.NAME FROM (SELECT M.RN, D.PRODUCT_ID, D.NAME FROM (SELECT T.DEPT_ID, T.PARENT_ID, SUBSTR(SYS_CONNECT_BY_PATH(T.DEPT_ID, '-'), 2) RN FROM DEPT T START WITH T.PARENT_ID IS NULL CONNECT BY PRIOR T.DEPT_ID = T.PARENT_ID) M, PRODUCT D WHERE M.DEPT_ID = D.DEPT_ID) K ORDER BY K.PRODUCT_ID
SELECT '1' AS DEPT_ID,'' AS PARENT_ID,'山東1' AS DEPT_NAME FROM DUAL
UNION ALL
SELECT '2' AS DEPT_ID,'1' AS PARENT_ID,'山東1-2' AS DEPT_NAME FROM DUAL
UNION ALL
SELECT '3' AS DEPT_ID,'1' AS PARENT_ID,'山東1-3' AS DEPT_NAME FROM DUAL
UNION ALL
SELECT '4' AS DEPT_ID,'2' AS PARENT_ID,'山東1-2-4' AS DEPT_NAME FROM DUAL
UNION ALL
SELECT '5' AS DEPT_ID,'2' AS PARENT_ID,'山東1-2-5' AS DEPT_NAME FROM DUAL
UNION ALL
SELECT '6' AS DEPT_ID,'3' AS PARENT_ID,'山東1-3-6' AS DEPT_NAME FROM DUAL
UNION ALL
SELECT '7' AS DEPT_ID,'3' AS PARENT_ID,'山東1-3-7' AS DEPT_NAME FROM DUAL
UNION ALL
SELECT '8' AS DEPT_ID,'3' AS PARENT_ID,'山東1-3-8' AS DEPT_NAME FROM DUAL
UNION ALL
SELECT '9' AS DEPT_ID,'' AS PARENT_ID,'山西1' AS DEPT_NAME FROM DUAL
UNION ALL
SELECT '10' AS DEPT_ID,'9' AS PARENT_ID,'山西1-2' AS DEPT_NAME FROM DUAL
),product AS (
SELECT '1' AS PRODUCT_ID,'6' AS DEPT_ID,'産品1' AS NAME FROM DUAL
UNION ALL
SELECT '2' AS PRODUCT_ID,'7' AS DEPT_ID,'産品2' AS NAME FROM DUAL
UNION ALL
SELECT '3' AS PRODUCT_ID,'8' AS DEPT_ID,'産品3' AS NAME FROM DUAL
UNION ALL
SELECT '4' AS PRODUCT_ID,'2' AS DEPT_ID,'産品4' AS NAME FROM DUAL
UNION ALL
SELECT '5' AS PRODUCT_ID,'10' AS DEPT_ID,'産品5' AS NAME FROM DUAL
)
select tb.*,ltrim(SYS_CONNECT_BY_PATH(DEPT_NAME, '==>'), '==>') as str
from (select dept.DEPT_ID,PARENT_ID,DEPT_NAME,NAME from dept left join product on dept.DEPT_ID = product.DEPT_ID) tb
start with PARENT_ID is null
connect by prior DEPT_ID = PARENT_ID
但是这个sql好像不太满足我的要求啊,我要的查询结果是:
在查询到某个一级部门下所有的产品的情况下同时把产品所属部门全部查询出来
要把所属部门一层一层的列出来,部门最多有3级这个刚才忘说明了,抱歉
效果是这样的
1级部门 2级部门 3级部门 产品id 产品名称
DEPT_NAME DEPT_NAME DEPT_NAME PRODUCT_ID NAME
'1' '1-3' '1-3-6' 1 '产品1'
'1' '1-3' '1-3-7' 2 '产品2'
'1' '1-3' '1-3-8' 3 '产品3'
'1' '1-2' 4 '产品4'
如果部门没有3级的话,其他的设为空
我的方法比较笨,好在结果是符合你的要求的。你看看,有问题再说
SELECT CASE
WHEN INSTR(K.RN, '-', -1, 2) > 0 THEN
SUBSTR(K.RN, 1, INSTR(K.RN, '-', -1, 2) - 1)
ELSE
NULL
END DEPT_NAME,
CASE
WHEN INSTR(K.RN, '-', -1, 1) > 0 THEN
SUBSTR(K.RN, 1, INSTR(K.RN, '-', -1, 1) - 1)
ELSE
NULL
END DEPT_NAME,
K.RN DEPT_NAME,
K.PRODUCT_ID,
K.NAME
FROM (SELECT M.RN, D.PRODUCT_ID, D.NAME
FROM (SELECT T.DEPT_ID,
T.PARENT_ID,
SUBSTR(SYS_CONNECT_BY_PATH(T.DEPT_ID, '-'), 2) RN
FROM DEPT T
START WITH T.PARENT_ID IS NULL
CONNECT BY PRIOR T.DEPT_ID = T.PARENT_ID) M,
PRODUCT D
WHERE M.DEPT_ID = D.DEPT_ID) K
ORDER BY K.PRODUCT_ID