SELECT DISTINCT MAX_DUTYSORT.ORGSORTID, MAX_DUTYSORT.INORGSORTID, A01.A0000, A01.A0101, A01.A0102, GB2261.CODE_ABR2 as A0104, A01.A0107, A01.A0111A, A01.A0114A, A01.A0117 as A0117code, A01.A0134, GB3304.CODE_ABR2 as A0117, GB4762.CODE_ABR2 as A0141, A0128, A0144, A0148, A0149, ZB125.CODE_ABR2 as A0160, ZB126.CODE_ABR2 as A0163, A0184, A0192, A0192A, B01.B0101 as A0195, A0196, A0187a, A0140, QRZXL, QRZXW, QRZXLXX, QRZXWXX, A1701, A14Z101, A15Z101, A57.A5714 as A0198 FROM A01 LEFT JOIN MAX_DUTYSORT ON A01.A0000 = MAX_DUTYSORT.A0000 LEFT JOIN GB2261 on A01.A0104 = GB2261.code LEFT JOIN GB3304 on A01.A0117 = GB3304.code LEFT JOIN GB4762 on A01.A0141 = GB4762.code LEFT JOIN B01 on A01.A0195 = B01.B0111 LEFT JOIN ZB125 on A01.A0160 = ZB125.code LEFT JOIN ZB126 on A01.A0163 = ZB126.code LEFT JOIN A57 on A01.A0000 = A57.A0000 WHERE 1 = 1 and A01.A0000 NOT IN (SELECT TOP 0 A01.A0000 FROM A01 LEFT JOIN MAX_DUTYSORT ON A01.A0000 = MAX_DUTYSORT.A0000 WHERE 1 = 1 ORDER BY A01.A0148 ASC, MAX_DUTYSORT.ORGSORTID ASC, MAX_DUTYSORT.INORGSORTID ASC) AND ROWNUM <= 2000 ORDER BY A01.A0148 ASC, MAX_DUTYSORT.ORGSORTID ASC, MAX_DUTYSORT.INORGSORTID ASC
oracle不支持SELECT TOP 2000的写法 oracle 对应的写法是 select * from table where rownum<=2000
SELECT TOP 2000 MAX_DUTYSORT.ORGSORTID, MAX_DUTYSORT.INORGSORTID, A01.A0000, A01.A0101, A01.A0102, GB2261.CODE_ABR2 as A0104, A01.A0107, A01.A0111A, A01.A0114A, A01.A0117 as A0117code, A01.A0134, GB3304.CODE_ABR2 as A0117, GB4762.CODE_ABR2 as A0141, A0128, A0144, A0148, A0149, ZB125.CODE_ABR2 as A0160, ZB126.CODE_ABR2 as A0163, A0184, A0192, A0192A, B01.B0101 as A0195, A0196, A0187a, A0140, QRZXL, QRZXW, QRZXLXX, QRZXWXX, A1701, A14Z101, A15Z101, A57.A5714 as A0198 FROM A01 LEFT JOIN MAX_DUTYSORT ON A01.A0000 = MAX_DUTYSORT.A0000 LEFT JOIN GB2261 on A01.A0104=GB2261.code LEFT JOIN GB3304 on A01.A0117=GB3304.code LEFT JOIN GB4762 on A01.A0141=GB4762.code LEFT JOIN B01 on A01.A0195=B01.B0111 LEFT JOIN ZB125 on A01.A0160=ZB125.code LEFT JOIN ZB126 on A01.A0163=ZB126.code LEFT JOIN A57 on A01.A0000=A57.A0000 WHERE 1=1 and A01.A0000 NOT IN ( SELECT TOP 0 A01.A0000 FROM A01 LEFT JOIN MAX_DUTYSORT ON A01.A0000 = MAX_DUTYSORT.A0000 WHERE 1=1 ORDER BY A01.A0148 ASC, MAX_DUTYSORT.ORGSORTID ASC, MAX_DUTYSORT.INORGSORTID ASC ) ORDER BY A01.A0148 ASC, MAX_DUTYSORT.ORGSORTID ASC, MAX_DUTYSORT.INORGSORTID ASC
SELECT DISTINCT MAX_DUTYSORT.ORGSORTID,
MAX_DUTYSORT.INORGSORTID,
A01.A0000,
A01.A0101,
A01.A0102,
GB2261.CODE_ABR2 as A0104,
A01.A0107,
A01.A0111A,
A01.A0114A,
A01.A0117 as A0117code,
A01.A0134,
GB3304.CODE_ABR2 as A0117,
GB4762.CODE_ABR2 as A0141,
A0128,
A0144,
A0148,
A0149,
ZB125.CODE_ABR2 as A0160,
ZB126.CODE_ABR2 as A0163,
A0184,
A0192,
A0192A,
B01.B0101 as A0195,
A0196,
A0187a,
A0140,
QRZXL,
QRZXW,
QRZXLXX,
QRZXWXX,
A1701,
A14Z101,
A15Z101,
A57.A5714 as A0198
FROM A01
LEFT JOIN MAX_DUTYSORT
ON A01.A0000 = MAX_DUTYSORT.A0000
LEFT JOIN GB2261
on A01.A0104 = GB2261.code
LEFT JOIN GB3304
on A01.A0117 = GB3304.code
LEFT JOIN GB4762
on A01.A0141 = GB4762.code
LEFT JOIN B01
on A01.A0195 = B01.B0111
LEFT JOIN ZB125
on A01.A0160 = ZB125.code
LEFT JOIN ZB126
on A01.A0163 = ZB126.code
LEFT JOIN A57
on A01.A0000 = A57.A0000
WHERE 1 = 1
and A01.A0000 NOT IN
(SELECT TOP 0 A01.A0000
FROM A01
LEFT JOIN MAX_DUTYSORT
ON A01.A0000 = MAX_DUTYSORT.A0000
WHERE 1 = 1
ORDER BY A01.A0148 ASC,
MAX_DUTYSORT.ORGSORTID ASC,
MAX_DUTYSORT.INORGSORTID ASC)
AND ROWNUM <= 2000
ORDER BY A01.A0148 ASC,
MAX_DUTYSORT.ORGSORTID ASC,
MAX_DUTYSORT.INORGSORTID ASC
oracle 对应的写法是
select * from table where rownum<=2000
MAX_DUTYSORT.ORGSORTID,
MAX_DUTYSORT.INORGSORTID,
A01.A0000,
A01.A0101,
A01.A0102,
GB2261.CODE_ABR2
as A0104,
A01.A0107,
A01.A0111A,
A01.A0114A,
A01.A0117 as A0117code,
A01.A0134,
GB3304.CODE_ABR2 as A0117,
GB4762.CODE_ABR2 as A0141,
A0128,
A0144,
A0148,
A0149,
ZB125.CODE_ABR2 as A0160,
ZB126.CODE_ABR2 as A0163,
A0184,
A0192,
A0192A,
B01.B0101 as A0195,
A0196,
A0187a,
A0140,
QRZXL,
QRZXW,
QRZXLXX,
QRZXWXX,
A1701,
A14Z101,
A15Z101,
A57.A5714 as A0198
FROM
A01
LEFT JOIN
MAX_DUTYSORT
ON
A01.A0000 = MAX_DUTYSORT.A0000
LEFT JOIN GB2261
on A01.A0104=GB2261.code
LEFT JOIN GB3304
on A01.A0117=GB3304.code
LEFT JOIN GB4762
on A01.A0141=GB4762.code
LEFT JOIN B01
on A01.A0195=B01.B0111
LEFT JOIN ZB125
on A01.A0160=ZB125.code LEFT JOIN
ZB126 on A01.A0163=ZB126.code LEFT JOIN A57
on A01.A0000=A57.A0000 WHERE 1=1 and A01.A0000
NOT IN (
SELECT TOP 0 A01.A0000 FROM A01 LEFT
JOIN MAX_DUTYSORT ON A01.A0000 = MAX_DUTYSORT.A0000
WHERE 1=1 ORDER BY A01.A0148 ASC,
MAX_DUTYSORT.ORGSORTID ASC, MAX_DUTYSORT.INORGSORTID ASC ) ORDER BY A01.A0148 ASC, MAX_DUTYSORT.ORGSORTID ASC, MAX_DUTYSORT.INORGSORTID ASC