如何改写带with的递归语句
现象描述
现在遇到应用里面有个递归查询的sql ，原来在pg 里面的写法是
创新互联公司是一家专业提供略阳企业网站建设,专注与网站设计制作、网站建设、H5建站、小程序制作等业务。10年已为略阳众多企业、政府机构等服务。创新互联专业网站设计公司优惠进行中。
with t ( c_id , c_name , n_level , level ) as (
select c_id , c_name , n_level , 1 as level from t_test_1 where c_pid is null
union
select c . c_id , c . c_name , c . n_level , level +1 from t join t_test_1 c on c . c_pid=t . c_id and t . level = level
)
select * from t ;
可是DM7 不支持这种写法，但是Oracle 和pg 都支持。
处理方法
这种with 语句很难理解，但是dm7 有更好的写法
select c . c_id , c . c_name , c . n_level , level
from t_test_1 c
start with c_pid is null
connect by prior c . c_id = c . c_pid ;
简单明了，还便于理解，并且Oracle 也是支持的，问题解决。
下面是建表语句和数据。
CREATE TABLE "SYSDBA" . "T_TEST_1"
(
"C_ID" VARCHAR ( 50 ) NULL ,
"C_NAME" VARCHAR ( 150 ) NULL ,
"C_PID" VARCHAR ( 50 ) NULL ,
"N_LEVEL" NUMERIC ( 3 , 0 ) NULL ,
"C_ALIAS" VARCHAR ( 100 ) NULL ,
"N_VALID" NUMERIC ( 3 , 0 ) NULL ,
"N_ORDER" NUMERIC ( 10 , 0 ) NULL ,
"DT_UPDATETIME" TIMESTAMP ( 3 ) NOT NULL ,
"J_EXT" TEXT NULL ,
"N_DEL" NUMERIC ( 3 , 0 ) NULL ,
"C_DM" VARCHAR ( 50 ) NULL ,
"C_GUID" VARCHAR ( 50 ) NULL ,
"C_GBM" VARCHAR ( 100 ) NULL ,
"C_DZ" VARCHAR ( 300 ) NULL ,
"C_YB" VARCHAR ( 300 ) NULL
);
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1049397442' , ' 日日结 ' , '94b92e44607d431e895483e921e7642e' , 3 , null , 1 , 232 , '2019-10-09 16:27:42.252' , '{"DTUpdatetime":"2019-10-09","CAdmin":"-2"}' , 2 , null , '1049397442' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '0' , ' 测试单位 00' , null , 1 , null , 1 , 1 , '2019-08-06 21:48:20.396' , '{"DTUpdatetime":"2019-08-06","CAdmin":"1718759404"}' , 2 , null , '0' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '140871093' , ' 测试单位 01' , '1727996873' , 3 , 'tewt' , 1 , 7 , '2019-08-13 08:35:32.282' , '{"DTUpdatetime":"2019-08-13","CAdmin":"-1"}' , 2 , null , '140871093' , null , ' 地址 ' , '12345' );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '141003694' , 'ZZ 荷塘 hYYdw' , '140945618' , 4 , null , 1 , 11 , '2019-08-13 08:36:22.259' , '{"DTUpdatetime":"2019-08-13","CAdmin":"-1"}' , 2 , null , '141003694' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '839112762' , ' 二级单位 ' , '778801089' , 3 , null , 1 , 16 , '2019-08-21 10:31:31.421' , '{"CAdmin":"-1"}' , 2 , null , '839112762' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '140945618' , ' 湖南省 ZZllYYdw' , '1727996873' , 3 , null , 1 , 8 , '2019-08-13 08:35:24.181' , '{"DTUpdatetime":"2019-08-13","CAdmin":"-1"}' , 2 , null , '140945618' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '141017105' , 'ZZ 石峰 hYYdw' , '140945618' , 4 , null , 1 , 12 , '2019-08-13 08:36:35.672' , '{"DTUpdatetime":"2019-08-13","CAdmin":"-1"}' , 2 , null , '141017105' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '4' , 'ztr 测试单位 ' , '0' , 2 , null , 1 , 27 , '2019-09-17 13:53:08.181' , '{"DTUpdatetime":"2019-09-17","CAdmin":"-1"}' , 2 , null , '4' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '59461700171' , 'dppatchdw 信息修改 ' , '0' , 1 , ' 简称 1' , 1 , 29 , '2019-09-17 13:53:08.181' , '{"CDh":"CDh2","DTUpdatetime":"2019-09-17","CKhhm":"CKhhm1","nSftb":1,"CAdmin":"CAdmin1","CKhyh":"CKhyh2","CKhzh":"CKhzh2"}' , 2 , 'CDm1' , '59461700171' , 'CGbm1' , 'CDz1' , 'CYb1' );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '10466045369' , 'dppatchdw 信息修改 ' , '0' , 1 , ' 简称 1' , 1 , 30 , '2019-09-17 13:53:08.181' , '{"CDh":"CDh2","DTUpdatetime":"2019-09-17","CKhhm":"CKhhm1","nSftb":1,"CAdmin":"CAdmin1","CKhyh":"CKhyh2","CKhzh":"CKhzh2"}' , 2 , 'CDm1' , '10466045369' , 'CGbm1' , 'CDz1' , 'CYb1' );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '10' , ' 郝 11' , '0' , 2 , null , 1 , 40 , '2019-09-17 13:53:08.181' , '{"DTUpdatetime":"2019-09-17","CAdmin":"-2"}' , 2 , null , '10' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '87903779266' , 'dppatchdw 信息修改 ' , '0' , 1 , ' 简称 1' , 1 , 41 , '2019-09-17 13:53:08.181' , '{"CDh":"CDh2","DTUpdatetime":"2019-09-17","CKhhm":"CKhhm1","nSftb":1,"CAdmin":"CAdmin1","CKhyh":"CKhyh2","CKhzh":"CKhzh2"}' , 2 , 'CDm1' , '87903779266' , 'CGbm1' , 'CDz1' , 'CYb1' );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '77644915859' , 'dppatchdw 信息修改 ' , '0' , 1 , ' 简称 1' , 1 , 42 , '2019-09-17 13:53:08.181' , '{"CDh":"CDh2","DTUpdatetime":"2019-09-17","CKhhm":"CKhhm1","nSftb":1,"CAdmin":"CAdmin1","CKhyh":"CKhyh2","CKhzh":"CKhzh2"}' , 2 , 'CDm1' , '77644915859' , 'CGbm1' , 'CDz1' , 'CYb1' );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '140966326' , 'ZZ 芙蓉 hYYdw' , '140871093' , 4 , null , 1 , 9 , '2019-08-13 08:35:44.889' , '{"DTUpdatetime":"2019-08-13","CAdmin":"-1"}' , 2 , null , '140966326' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( 'd014cba5da844be68d36d08e4bcd2cd2' , ' 删除单位 1 单位 ' , '58439d7e6c20474ca014a60b83b7afa3' , 3 , null , 1 , 22 , '2019-08-28 10:26:38.809' , '{"DTUpdatetime":"2019-08-28","CAdmin":"-1"}' , 2 , null , 'd014cba5da844be68d36d08e4bcd2cd2' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1660690291' , ' 测试单位 sjjy2' , '1660471275' , 3 , ' 测试单位 sjjy2' , 1 , 3 , '2019-04-28 16:07:30.716' , '{"CAdmin":"-2"}' , 2 , null , '1660690291' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1660888553' , ' 测试单位 sjjy3' , '1660690291' , 4 , ' 测试单位 sjjy3' , 1 , 4 , '2019-04-28 16:10:48.977' , '{"CAdmin":"-2"}' , 2 , null , '1660888553' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1661097831' , ' 测试单位 sjjy4' , '1660690291' , 4 , ' 测试单位 sjjy4' , 1 , 5 , '2019-04-28 16:14:18.253' , '{"CAdmin":"-2"}' , 2 , null , '1661097831' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1661315634' , ' 测试单位 sjjy5' , '1660690291' , 4 , ' 测试单位 sjjy5' , 1 , 6 , '2019-04-28 16:17:56.058' , '{"CAdmin":"-2"}' , 2 , null , '1661315634' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1661525393' , ' 测试单位 sjjy6' , '1660690291' , 4 , ' 测试单位 sjjy6' , 1 , 7 , '2019-04-28 16:21:25.817' , '{"CAdmin":"-2"}' , 2 , null , '1661525393' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1661735731' , ' 测试单位 sjjy7' , '1660690291' , 4 , ' 测试单位 sjjy7' , 1 , 8 , '2019-04-28 16:24:56.156' , '{"CAdmin":"-2"}' , 2 , null , '1661735731' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1661952065' , ' 测试单位 sjjy8' , '1660690291' , 4 , ' 测试单位 sjjy8' , 1 , 9 , '2019-04-28 16:28:32.489' , '{"CAdmin":"-2"}' , 2 , null , '1661952065' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1662162804' , ' 测试单位 sjjy9' , '1660690291' , 4 , ' 测试单位 sjjy9' , 1 , 10 , '2019-04-28 16:32:03.228' , '{"CAdmin":"-2"}' , 2 , null , '1662162804' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1662379814' , ' 测试单位 sjjy10' , '1660690291' , 4 , ' 测试单位 sjjy10' , 1 , 11 , '2019-04-28 16:35:40.238' , '{"CAdmin":"-2"}' , 2 , null , '1662379814' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1662596265' , ' 测试单位 sjjy11' , '1660690291' , 4 , ' 测试单位 sjjy11' , 1 , 12 , '2019-04-28 16:39:16.689' , '{"CAdmin":"-2"}' , 2 , null , '1662596265' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1662808665' , ' 测试单位 sjjy12' , '1663241559' , 4 , ' 测试单位 sjjy12' , 1 , 13 , '2019-04-28 16:42:49.089' , '{"CAdmin":"-2"}' , 2 , null , '1662808665' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1663024610' , ' 测试单位 sjjy13' , '1663241559' , 4 , ' 测试单位 sjjy13' , 1 , 14 , '2019-04-28 16:46:25.034' , '{"CAdmin":"-2"}' , 2 , null , '1663024610' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1663241559' , ' 测试单位 sjjy14' , '1660471275' , 3 , ' 测试单位 sjjy14' , 1 , 15 , '2019-04-28 16:50:01.984' , '{"CAdmin":"-2"}' , 2 , null , '1663241559' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1663463520' , ' 测试单位 sjjy15' , '1663241559' , 4 , ' 测试单位 sjjy15' , 1 , 16 , '2019-04-28 16:53:43.945' , '{"CAdmin":"-2"}' , 2 , null , '1663463520' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1663666788' , ' 测试单位 sjjy16' , '1663241559' , 4 , ' 测试单位 sjjy16' , 1 , 17 , '2019-04-28 16:57:07.212' , '{"CAdmin":"-2"}' , 2 , null , '1663666788' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1663878761' , ' 测试单位 sjjy17' , '1663241559' , 4 , ' 测试单位 sjjy17' , 1 , 18 , '2019-04-28 17:00:39.185' , '{"CAdmin":"-2"}' , 2 , null , '1663878761' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1664091424' , ' 测试单位 sjjy18' , '1663241559' , 4 , ' 测试单位 sjjy18' , 1 , 19 , '2019-04-28 17:04:11.848' , '{"CAdmin":"-2"}' , 2 , null , '1664091424' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1664294633' , ' 测试单位 sjjy19' , '1663241559' , 4 , ' 测试单位 sjjy19' , 1 , 20 , '2019-04-28 17:07:35.057' , '{"CAdmin":"-2"}' , 2 , null , '1664294633' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1664508388' , ' 测试单位 sjjy20' , '1663241559' , 4 , ' 测试单位 sjjy20' , 1 , 21 , '2019-04-28 17:11:08.813' , '{"CAdmin":"-2"}' , 2 , null , '1664508388' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '8' , ' 郝 aaa9' , '0' , 2 , 'aaa9' , 1 , 38 , '2019-09-29 14:18:54.967' , '{"DTUpdatetime":"2019-09-29","CAdmin":"-2"}' , 2 , null , '8' , null , 'aaa9' , 'aaa9' );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '41128488855' , 'dppatchdw 信息修改 ' , '0' , 1 , ' 简称 1' , 1 , 222 , '2019-09-23 17:08:53.705' , '{"CDh":"CDh2","DTUpdatetime":"2019-09-23","CKhhm":"CKhhm1","nSftb":1,"CAdmin":"CAdmin1","CKhyh":"CKhyh2","CKhzh":"CKhzh2"}' , 2 , 'CDm1' , '41128488855' , 'CGbm1' , 'CDz1' , 'CYb1' );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1664722744' , ' 测试单位 sjjy21' , '1663241559' , 4 , ' 测试单位 sjjy21' , 1 , 22 , '2019-04-28 17:14:43.168' , '{"CAdmin":"-2"}' , 2 , null , '1664722744' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1664941688' , ' 测试单位 sjjy22' , '1663241559' , 4 , ' 测试单位 sjjy22' , 1 , 23 , '2019-04-28 17:18:22.112' , '{"CAdmin":"-2"}' , 2 , null , '1664941688' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1665164344' , ' 测试单位 sjjy23' , '1663241559' , 4 , ' 测试单位 sjjy23' , 1 , 24 , '2019-04-28 17:22:04.769' , '{"CAdmin":"-2"}' , 2 , null , '1665164344' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1665379625' , ' 测试单位 sjjy24' , '1665816110' , 4 , ' 测试单位 sjjy24' , 1 , 25 , '2019-04-28 17:25:40.050' , '{"CAdmin":"-2"}' , 2 , null , '1665379625' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1665590972' , ' 测试单位 sjjy25' , '1665816110' , 4 , ' 测试单位 sjjy25' , 1 , 26 , '2019-04-28 17:29:11.397' , '{"CAdmin":"-2"}' , 2 , null , '1665590972' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1665816110' , ' 测试单位 sjjy26' , '1660471275' , 3 , ' 测试单位 sjjy26' , 1 , 27 , '2019-04-28 17:32:56.533' , '{"CAdmin":"-2"}' , 2 , null , '1665816110' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1666042586' , ' 测试单位 sjjy27' , '1665816110' , 4 , ' 测试单位 sjjy27' , 1 , 28 , '2019-04-28 17:36:43.008' , '{"CAdmin":"-2"}' , 2 , null , '1666042586' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1666265935' , ' 测试单位 sjjy28' , '1665816110' , 4 , ' 测试单位 sjjy28' , 1 , 29 , '2019-04-28 17:40:26.359' , '{"CAdmin":"-2"}' , 2 , null , '1666265935' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1666491476' , ' 测试单位 sjjy29' , '1665816110' , 4 , ' 测试单位 sjjy29' , 1 , 30 , '2019-04-28 17:44:11.900' , '{"CAdmin":"-2"}' , 2 , null , '1666491476' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1666715962' , ' 测试单位 sjjy30' , '1665816110' , 4 , ' 测试单位 sjjy30' , 1 , 31 , '2019-04-28 17:47:56.385' , '{"CAdmin":"-2"}' , 2 , null , '1666715962' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1666931264' , ' 测试单位 sjjy31' , '1665816110' , 4 , ' 测试单位 sjjy31' , 1 , 32 , '2019-04-28 17:51:31.689' , '{"CAdmin":"-2"}' , 2 , null , '1666931264' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1667150777' , ' 测试单位 sjjy32' , '1665816110' , 4 , ' 测试单位 sjjy32' , 1 , 33 , '2019-04-28 17:55:11.201' , '{"CAdmin":"-2"}' , 2 , null , '1667150777' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1667382212' , ' 测试单位 sjjy33' , '1665816110' , 4 , ' 测试单位 sjjy33' , 1 , 34 , '2019-04-28 17:59:02.637' , '{"CAdmin":"-2"}' , 2 , null , '1667382212' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1667626580' , ' 测试单位 sjjy34' , '1665816110' , 4 , ' 测试单位 sjjy34' , 1 , 35 , '2019-04-28 18:03:07.004' , '{"CAdmin":"-2"}' , 2 , null , '1667626580' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1667859807' , ' 测试单位 sjjy35' , '1665816110' , 4 , ' 测试单位 sjjy35' , 1 , 36 , '2019-04-28 18:07:00.229' , '{"CAdmin":"-2"}' , 2 , null , '1667859807' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1668090455' , ' 测试单位 sjjy36' , '1668543609' , 4 , ' 测试单位 sjjy36' , 1 , 37 , '2019-04-28 18:10:50.880' , '{"CAdmin":"-2"}' , 2 , null , '1668090455' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1668317596' , ' 测试单位 sjjy37' , '1668543609' , 4 , ' 测试单位 sjjy37' , 1 , 38 , '2019-04-28 18:14:38.021' , '{"CAdmin":"-2"}' , 2 , null , '1668317596' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1668543609' , ' 测试单位 sjjy38' , '1660471275' , 3 , ' 测试单位 sjjy38' , 1 , 39 , '2019-04-28 18:18:24.033' , '{"CAdmin":"-2"}' , 2 , null , '1668543609' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1668774831' , ' 测试单位 sjjy39' , '1668543609' , 4 , ' 测试单位 sjjy39' , 1 , 40 , '2019-04-28 18:22:15.255' , '{"CAdmin":"-2"}' , 2 , null , '1668774831' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1668998668' , ' 测试单位 sjjy40' , '1668543609' , 4 , ' 测试单位 sjjy40' , 1 , 41 , '2019-04-28 18:25:59.093' , '{"CAdmin":"-2"}' , 2 , null , '1668998668' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1669220950' , ' 测试单位 sjjy41' , '1668543609' , 4 , ' 测试单位 sjjy41' , 1 , 42 , '2019-04-28 18:29:41.375' , '{"CAdmin":"-2"}' , 2 , null , '1669220950' , null , null , null );
INSERT INTO "SYSDBA" . "T_TEST_1" VALUES ( '1669450257' , ' 测试单位 sjjy42' , '1668543609' , 4 , ' 测试单位 sjjy42' , 1 , 43 , '2019-04-28 18:33:30.678' , '{"CAdmin":"-2"}' , 2 , null , '1669450257' , null , null , null );
本文题目：如何改写带with的递归语句
当前链接：http://pwwzsj.com/article/pcjohs.html
