1、建表语句
CREATE TABLE "hxdb"."sys_area" (
"id" varchar(64) COLLATE "default" NOT NULL,
"code" varchar(64) COLLATE "default",
"province" varchar(64) COLLATE "default",
"city" varchar(64) COLLATE "default",
"district" varchar(64) COLLATE "default",
"parent" varchar(64) COLLATE "default"
)
WITH (OIDS=FALSE);
2、查询Sql语句
WITH RECURSIVE cte AS (
SELECT
ID,
code,
province,
city,
district,
parent
FROM
hxdb.sys_area A
WHERE
parent = '1'
UNION ALL
SELECT
K . ID,
K .code,
K .province,
K .city,
K .district,
K .parent
FROM
hxdb.sys_area K
INNER JOIN cte C ON C .parent = K . ID
) SELECT
ID,
code,
province,
city,
district,
parent
FROM
hxdb.sys_area cte
WHERE
province != ''
OR city != ''
OR district != ''