insert语句分析,该如何解决

insert语句分析
我在一个连接db2的.net程序里抽取了一个sql语句，看不懂，兄妹们帮忙分析一下语法，insert into .. with .. as ..看不懂，谢谢！
第一个insert：

SQL code

INSERT INTO PF.T_ANALYSE_NODE(NODEID, SID) WITH TOPO (ID, PAPERID, ROOTDEVID, ORDER_NUM, FS_ID, TS_ID,  FNODE_DEV_ID, TNODE_DEV_ID) AS (SELECT ID, PAPERID, ROOTDEVID, ORDER_NUM, FS_ID, TS_ID, FNODE_DEV_ID, TNODE_DEV_ID     FROM PF.T_POWER_TOPO     WHERE FNODE_DEV_ID = ? AND FS_ID = ? AND PAPERID = ? AND TS_ID IS NULL     UNION ALL     SELECT CHILD.ID, CHILD.PAPERID, CHILD.ROOTDEVID, CHILD.ORDER_NUM, CHILD.FS_ID, CHILD.TS_ID, CHILD.FNODE_DEV_ID, CHILD.TNODE_DEV_ID     FROM TOPO PARENT , PF.T_POWER_TOPO CHILD     WHERE PARENT.TNODE_DEV_ID=CHILD.FNODE_DEV_ID AND CHILD.PAPERID = ? AND CHILD.TS_ID IS NULL)SELECT 6226, T.TNODE_DEV_ID FROM TOPO T

第二个insert和第一个类似：

SQL code

INSERT INTO PF.T_ANALYSE_TOPO(NODEID, SID, POWERID, STATUS, SUBSID, TERMINALID) WITH TOPO (ID, PAPERID, ROOTDEVID, ORDER_NUM, FS_ID, TS_ID,  FNODE_DEV_ID, TNODE_DEV_ID) AS (SELECT ID, PAPERID, ROOTDEVID, ORDER_NUM, FS_ID, TS_ID, FNODE_DEV_ID, TNODE_DEV_ID     FROM PF.T_POWER_TOPO     WHERE FNODE_DEV_ID = ? AND FS_ID = ? AND PAPERID = ? AND TS_ID IS NULL     UNION ALL     SELECT CHILD.ID, CHILD.PAPERID, CHILD.ROOTDEVID, CHILD.ORDER_NUM, CHILD.FS_ID, CHILD.TS_ID, CHILD.FNODE_DEV_ID, CHILD.TNODE_DEV_ID     FROM TOPO PARENT , PF.T_POWER_TOPO CHILD     WHERE PARENT.TNODE_DEV_ID=CHILD.FNODE_DEV_ID AND CHILD.PAPERID = ? AND CHILD.TS_ID IS NULL) SELECT 6226, T2.TNODE_DEV_ID, 0, T2.TNODE_STATUS_TYPE, 0, T2.TS_ID FROM TOPO T1 JOIN PF.T_POWER_TOPO T2 ON (T1.TNODE_DEV_ID = T2.FNODE_DEV_ID AND T1.PAPERID = T2.PAPERID AND T2.TS_ID IS NOT NULL) UNION ALL SELECT 6226, T3.TNODE_DEV_ID, 0, T3.TNODE_STATUS_TYPE, 0, T3.TS_ID  FROM PF.T_POWER_TOPO T3 WHERE T3.FNODE_DEV_ID = ? AND T3.FS_ID = ? AND T3.PAPERID = ? AND T3.TS_ID IS NOT NULL UNION ALL SELECT DISTINCT 6226, T4.FNODE_DEV_ID, 0, T4.FNODE_STATUS_TYPE, 0, T4.FS_ID  FROM PF.T_POWER_TOPO T4 WHERE T4.FNODE_DEV_ID = ? AND T4.FS_ID = ? AND T4.PAPERID = ? 

[解决办法]
1 INSERT INTO PF.T_ANALYSE_NODE(NODEID, SID)
2 WITH TOPO (ID, PAPERID, ROOTDEVID, ORDER_NUM, FS_ID, TS_ID, FNODE_DEV_ID, TNODE_DEV_ID)
AS (SELECT ID, PAPERID, ROOTDEVID, ORDER_NUM, FS_ID, TS_ID, FNODE_DEV_ID, TNODE_DEV_ID
FROM PF.T_POWER_TOPO
WHERE FNODE_DEV_ID = ? AND FS_ID = ? AND PAPERID = ? AND TS_ID IS NULL
UNION ALL
SELECT CHILD.ID, CHILD.PAPERID, CHILD.ROOTDEVID, CHILD.ORDER_NUM, CHILD.FS_ID, CHILD.TS_ID, CHILD.FNODE_DEV_ID, CHILD.TNODE_DEV_ID
FROM TOPO PARENT , PF.T_POWER_TOPO CHILD
WHERE PARENT.TNODE_DEV_ID=CHILD.FNODE_DEV_ID AND CHILD.PAPERID = ? AND CHILD.TS_ID IS NULL)
3 SELECT 6226, T.TNODE_DEV_ID FROM TOPO T

你的插入语句的结果集来自一个公用表表达式的查询结果
首先，语句2是一个定义为TOPO(ID, PAPERID, ROOTDEVID, ORDER_NUM, FS_ID, TS_ID, FNODE_DEV_ID, TNODE_DEV_ID)的公用表表达式，as后面就像定义了一个视图，定义了TOPO。
语句3定义了要插入的查询结果，即语句1要插入的结果
[解决办法]
1，2两个insert语句结构确实是一样的。with以后的部分是一个公共表达式，你可以把它当成一个select语句，那么1，2两个SQL就可以看成是insert into...select ...。

当然，如果你还想知道那两个with到底是怎么取数据的，可以咨询你们的数据库工程师，或者查一下with的语法结构。

查看更多 下一篇