test data:code: SELECT 'SEQ' || ROWNUM ID, DECODE(TO_CHAR(T.ID), '0', '0', 'SEQ' || T.ID) FID, T.NM NAME, T.SHUX FROM (SELECT DISTINCT 1 SEQ, 0 ID, T1.NAME1 NM, T1.NO1 SHUX FROM (SELECT * FROM TEST T1 ORDER BY T1.NAME1) T1 UNION ALL SELECT 2 SEQ, T2.SEQ ID, T1.NAME2 NM, T1.NO2 FROM TEST T1, (SELECT ROWNUM SEQ, T1.NM, T1.ID, T1.SHUX FROM (SELECT DISTINCT T1.NAME1 NM, 0 ID, T1.NO1 SHUX FROM TEST T1 ORDER BY T1.NAME1) T1) T2 WHERE T1.NAME1 = T2.NM ORDER BY SEQ, ID, NM) T;result:
SELECT ROWNUM ID, T.ID FID, T.NM NAME, T.SHUX FROM (SELECT DISTINCT 1 SEQ, 0 ID, T1.NAME1 NM, T1.NO1 SHUX FROM (SELECT * FROM TEST T1 ORDER BY T1.NAME1) T1 UNION ALL SELECT 2 SEQ, T2.SEQ ID, T1.NAME2 NM, T1.NO2 FROM TEST T1, (SELECT ROWNUM SEQ, T1.NM, T1.ID, T1.SHUX FROM (SELECT DISTINCT T1.NAME1 NM, 0 ID, T1.NO1 SHUX FROM TEST T1 ORDER BY T1.NAME1) T1) T2 WHERE T1.NAME1 = T2.NM ORDER BY SEQ, ID, NM) T;上面的SQL不行吗,直接用rownum代替就行了啊,还不需要处理啊
SELECT ROWNUM ID, T.ID FID, T.NM NAME, T.SHUX FROM (SELECT DISTINCT 1 SEQ, 0 ID, T1.NAME1 NM, T1.NO1 SHUX FROM (SELECT * FROM TEST T1 ORDER BY T1.NAME1) T1 UNION ALL SELECT 2 SEQ, T2.SEQ ID, T1.NAME2 NM, T1.NO2 FROM TEST T1, (SELECT ROWNUM SEQ, T1.NM, T1.ID, T1.SHUX FROM (SELECT DISTINCT T1.NAME1 NM, 0 ID, T1.NO1 SHUX FROM TEST T1 ORDER BY T1.NAME1) T1) T2 WHERE T1.NAME1 = T2.NM ORDER BY SEQ, ID, NM) T;上面的SQL不行吗,直接用rownum代替就行了啊,还不需要处理啊
seq5 seq1 小类3 15
seq5 seq1 小类4 18
SELECT 'SEQ' || ROWNUM ID,
DECODE(TO_CHAR(T.ID), '0', '0', 'SEQ' || T.ID) FID,
T.NM NAME,
T.SHUX
FROM (SELECT DISTINCT 1 SEQ, 0 ID, T1.NAME1 NM, T1.NO1 SHUX
FROM (SELECT * FROM TEST T1 ORDER BY T1.NAME1) T1
UNION ALL
SELECT 2 SEQ, T2.SEQ ID, T1.NAME2 NM, T1.NO2
FROM TEST T1,
(SELECT ROWNUM SEQ, T1.NM, T1.ID, T1.SHUX
FROM (SELECT DISTINCT T1.NAME1 NM, 0 ID, T1.NO1 SHUX
FROM TEST T1
ORDER BY T1.NAME1) T1) T2
WHERE T1.NAME1 = T2.NM
ORDER BY SEQ, ID, NM) T;result:
seq5 seq1 小类3 15
seq5 seq1 小类4 18
一样就是 写错了的
SELECT ROWNUM ID, T.ID FID, T.NM NAME, T.SHUX
FROM (SELECT DISTINCT 1 SEQ, 0 ID, T1.NAME1 NM, T1.NO1 SHUX
FROM (SELECT * FROM TEST T1 ORDER BY T1.NAME1) T1
UNION ALL
SELECT 2 SEQ, T2.SEQ ID, T1.NAME2 NM, T1.NO2
FROM TEST T1,
(SELECT ROWNUM SEQ, T1.NM, T1.ID, T1.SHUX
FROM (SELECT DISTINCT T1.NAME1 NM, 0 ID, T1.NO1 SHUX
FROM TEST T1
ORDER BY T1.NAME1) T1) T2
WHERE T1.NAME1 = T2.NM
ORDER BY SEQ, ID, NM) T;上面的SQL不行吗,直接用rownum代替就行了啊,还不需要处理啊
SELECT ROWNUM ID, T.ID FID, T.NM NAME, T.SHUX
FROM (SELECT DISTINCT 1 SEQ, 0 ID, T1.NAME1 NM, T1.NO1 SHUX
FROM (SELECT * FROM TEST T1 ORDER BY T1.NAME1) T1
UNION ALL
SELECT 2 SEQ, T2.SEQ ID, T1.NAME2 NM, T1.NO2
FROM TEST T1,
(SELECT ROWNUM SEQ, T1.NM, T1.ID, T1.SHUX
FROM (SELECT DISTINCT T1.NAME1 NM, 0 ID, T1.NO1 SHUX
FROM TEST T1
ORDER BY T1.NAME1) T1) T2
WHERE T1.NAME1 = T2.NM
ORDER BY SEQ, ID, NM) T;上面的SQL不行吗,直接用rownum代替就行了啊,还不需要处理啊
非常感谢