1、为避免因为使用了重复的别名导致查询分析过程的混淆,将第一查询修改如下: ------------------------------------------------------------------------ SELECT * FROM (SELECT o.MNID, max(to_number(to_char(o.tstdate)||'000000') + o.tsttime) as ot, o.tsttime as tsttime FROM WIPPCBA o WHERE o.model = 'D92' AND o.SEGMENT = 'PCBA' AND o.process = 'PTEST' AND o.pcsseq IN ('A', 'L') AND o.MNID IN ('D92000005081716904') GROUP BY o.MNID,o.tsttime) m, (SELECT i.MNID, min( to_number(to_char(i.tstdate)||'000000')) as it FROM WIPPCBA i WHERE i.model = 'D92' AND i.SEGMENT = 'PCBA' AND i.process = 'PTEST' AND i.pcsseq IN ('A', 'F') AND i.MNID IN ('D92000005081716904') GROUP BY i.MNID,i.tsttime) n WHERE m.MNID = n.MNID
try: ------------------------------------------------------------------------------ INSERT INTO cplfgcycledata (id, sftdate, plant, pdline, cid, facility, SEGMENT, sono, submodel, model, partno, mnid, snid, idate, itime, odate, otime, holiday,lmdate, lmtime,reserve1, reserve2, reserve3) SELECT 1 AS id, 20050222 AS sftdate, 1 AS plant, 'EU' AS pdline, ' ' AS cid, 0 AS facility, 'PCBA' AS SEGMENT, 0 AS sono, 'D92' AS submodel, 'D92' AS model, ' ' AS partno, i.MNID AS MNID, 0 AS snid, ROUND (i.it / 1000000) AS idate, MOD (i.it, 1000000) AS itime, ROUND (o.ot / 1000000) AS odate, MOD (o.ot, 1000000) AS otime, -1 AS holiday, 20050615 AS lmdate, 105240 AS lmtime, ' ' AS reserve1, ' ' AS reserve2, '0' AS reserve3 FROM (SELECT i.MNID, MIN (NVL (i.tstdate, 0) * 1000000+ NVL (i.tsttime, 0)) AS it FROM WIPPCBA i WHERE i.model = 'D92' AND i.SEGMENT = 'PCBA' AND i.process = 'PTEST' AND i.pcsseq IN ('A', 'F') AND i.MNID IN ('D92000005081716904') GROUP BY i.MNID) m, (SELECT o.MNID, MAX (NVL (o.tstdate, 0) * 1000000+ NVL (o.tsttime, 0)) AS ot FROM WIPPCBA o WHERE o.model = 'D92' AND o.SEGMENT = 'PCBA' AND o.process = 'PTEST' AND o.pcsseq IN ('A', 'L') AND o.MNID IN ('D92000005081716904') GROUP BY o.MNID) n WHERE m.MNID = n.MNID
------------------------------------------------------------------------
SELECT
*
FROM
(SELECT
o.MNID,
max(to_number(to_char(o.tstdate)||'000000') + o.tsttime) as ot,
o.tsttime as tsttime
FROM
WIPPCBA o
WHERE
o.model = 'D92'
AND
o.SEGMENT = 'PCBA'
AND
o.process = 'PTEST'
AND
o.pcsseq IN ('A', 'L')
AND
o.MNID IN ('D92000005081716904')
GROUP BY
o.MNID,o.tsttime) m,
(SELECT
i.MNID,
min( to_number(to_char(i.tstdate)||'000000')) as it
FROM
WIPPCBA i
WHERE
i.model = 'D92'
AND
i.SEGMENT = 'PCBA'
AND
i.process = 'PTEST'
AND
i.pcsseq IN ('A', 'F')
AND
i.MNID IN ('D92000005081716904')
GROUP BY
i.MNID,i.tsttime) n
WHERE
m.MNID = n.MNID
------------------------------------------------------------------------------
INSERT INTO cplfgcycledata
(id, sftdate, plant, pdline, cid,
facility, SEGMENT, sono, submodel,
model, partno, mnid, snid, idate,
itime, odate, otime, holiday,lmdate,
lmtime,reserve1, reserve2, reserve3)
SELECT
1 AS id,
20050222 AS sftdate,
1 AS plant,
'EU' AS pdline,
' ' AS cid,
0 AS facility,
'PCBA' AS SEGMENT,
0 AS sono,
'D92' AS submodel,
'D92' AS model,
' ' AS partno,
i.MNID AS MNID,
0 AS snid,
ROUND (i.it / 1000000) AS idate,
MOD (i.it, 1000000) AS itime,
ROUND (o.ot / 1000000) AS odate,
MOD (o.ot, 1000000) AS otime,
-1 AS holiday,
20050615 AS lmdate,
105240 AS lmtime,
' ' AS reserve1,
' ' AS reserve2,
'0' AS reserve3
FROM
(SELECT
i.MNID,
MIN (NVL (i.tstdate, 0) * 1000000+ NVL (i.tsttime, 0)) AS it
FROM
WIPPCBA i
WHERE
i.model = 'D92'
AND
i.SEGMENT = 'PCBA'
AND
i.process = 'PTEST'
AND
i.pcsseq IN ('A', 'F')
AND
i.MNID IN ('D92000005081716904')
GROUP BY
i.MNID) m,
(SELECT
o.MNID,
MAX (NVL (o.tstdate, 0) * 1000000+ NVL (o.tsttime, 0)) AS ot
FROM
WIPPCBA o
WHERE
o.model = 'D92'
AND
o.SEGMENT = 'PCBA'
AND
o.process = 'PTEST'
AND
o.pcsseq IN ('A', 'L')
AND
o.MNID IN ('D92000005081716904')
GROUP BY
o.MNID) n
WHERE
m.MNID = n.MNID
表A,B,栏位都相同
A.a A.b A.c A.d A.e
1 2 3 -1 -1
1 -1 -1 4 5
2 4 1 -1 -1
2 -1 -1 6 3写一个查询,得到
A.a A.b A.c A.d A.e
1 2 3 4 5
2 4 1 6 3