【DB2】国标行业分类存储，通过SQL查询出层级关系

新建表

DROP TABLE Industry;
CREATE TABLE Industry(
IndustryCode VARCHAR(40),IndustryName VARCHAR(100),ParentID VARCHAR(40)
)

点击下载插入语句

数据截图：

现在数据是这个样子的，我们需要找出对应的层级关系,例如：

实现SQL如下：

SELECT
A.IndustryCode AS CLASS01,A.INDUSTRYNAME CLASS_01NM,
B.IndustryCode AS CLASS02,B.INDUSTRYNAME CLASS_02NM,
C.IndustryCode AS CLASS03,C.INDUSTRYNAME CLASS_03NM,
C.IndustryCode AS CLASS04,C.INDUSTRYNAME CLASS_04NM,
DENSE_RANK() over(ORDER BY A.INDUSTRYCODE) SEQ
FROM Industry A,Industry B,Industry C,Industry D
WHERE A.INDUSTRYCODE=B.PARENTID AND B.INDUSTRYCODE=C.PARENTID AND C.INDUSTRYCODE=D.PARENTID and length(A.IndustryCode)=1

特别说明：

如果说存在越级维那么上述SQL则会出现问题

【内连接】

WITH DT(VALUE,NAME,PARENT)
AS
(
SELECT 'A','中国',NULL FROM SYSIBM.DUAL UNION ALL
SELECT 'A01','北京','A' FROM SYSIBM.DUAL UNION ALL
SELECT 'A02','云南','A' FROM SYSIBM.DUAL UNION ALL
SELECT 'A011','昆明','A02' FROM SYSIBM.DUAL UNION ALL
SELECT 'AA12','曲靖','A02' FROM SYSIBM.DUAL
)

SELECT A.VALUE AVALUE,A.NAME ANAME,B.VALUE BVALUE,B.NAME BNAME FROM DT A,DT B WHERE A.VALUE=B.PARENT
AND LENGTH(A.VALUE)='3'

运行结果

【左连接】

WITH DT(VALUE,NAME,PARENT)
AS
(
SELECT 'A','中国',NULL FROM SYSIBM.DUAL UNION ALL
SELECT 'A01','北京','A' FROM SYSIBM.DUAL UNION ALL
SELECT 'A02','云南','A' FROM SYSIBM.DUAL UNION ALL
SELECT 'A011','昆明','A02' FROM SYSIBM.DUAL UNION ALL
SELECT 'AA12','曲靖','A02' FROM SYSIBM.DUAL
)

SELECT A.VALUE AVALUE,A.NAME ANAME,B.VALUE BVALUE,B.NAME BNAME
FROM DT A LEFT JOIN DT B ON A.VALUE=B.PARENT
WHERE LENGTH(A.VALUE)='3'

由此可见，最好使用左连接，不然层级出现越级维的时候就会出现数据缺失。

转载于:https://www.cnblogs.com/OliverQin/p/8662789.html