这个贴在db2里有一个,没人回答,所以再开一贴。我在一个连接db2的.net程序里抽取了一个sql语句,看不懂,兄妹们帮忙分析一下语法,insert into .. with .. as ..看不懂,谢谢!
第一个insert: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和第一个类似: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 = ?
第一个insert: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和第一个类似: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 = ?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货