Code1:
SELECT a.mps_id, b.MONTH
FROM (SELECT mps_id
FROM smicscmadm.scm_mps_mast
WHERE del_flag = 'N' AND TYPE = 'MPS' AND ROWNUM <= 6
ORDER BY mps_id DESC) a,
(SELECT mps_id, YEAR || MONTH AS MONTH
FROM scm_mps_qty) b
WHERE a.mps_id = b.mps_idCode2:
SELECT DISTINCT a.mps_id, b.MONTH
FROM (SELECT mps_id
FROM smicscmadm.scm_mps_mast
WHERE del_flag = 'N' AND TYPE = 'MPS' AND ROWNUM <= 6
ORDER BY mps_id DESC) a,
(SELECT mps_id, YEAR || MONTH AS MONTH
FROM scm_mps_qty) b
WHERE a.mps_id = b.mps_idCode1 在结束后有返回结果,但是Code2在执行结束后没有返回结果,Code2相对于Code1的唯一区别是加了distinct 关键字. db是Oracle 11g希望大家帮忙看看是哪里写错了.
SELECT a.mps_id, b.MONTH
FROM (SELECT mps_id
FROM smicscmadm.scm_mps_mast
WHERE del_flag = 'N' AND TYPE = 'MPS' AND ROWNUM <= 6
ORDER BY mps_id DESC) a,
(SELECT mps_id, YEAR || MONTH AS MONTH
FROM scm_mps_qty) b
WHERE a.mps_id = b.mps_idCode2:
SELECT DISTINCT a.mps_id, b.MONTH
FROM (SELECT mps_id
FROM smicscmadm.scm_mps_mast
WHERE del_flag = 'N' AND TYPE = 'MPS' AND ROWNUM <= 6
ORDER BY mps_id DESC) a,
(SELECT mps_id, YEAR || MONTH AS MONTH
FROM scm_mps_qty) b
WHERE a.mps_id = b.mps_idCode1 在结束后有返回结果,但是Code2在执行结束后没有返回结果,Code2相对于Code1的唯一区别是加了distinct 关键字. db是Oracle 11g希望大家帮忙看看是哪里写错了.
scm_mps_qty
1,2 两次查询上述两表的数据是否不一样啊
SELECT A.MPS_ID, B.MONTH
FROM (SELECT MPS_ID
FROM (SELECT ROWNUM RID, MPS_ID
FROM SMICSCMADM.SCM_MPS_MAST
WHERE DEL_FLAG = 'N'
AND TYPE = 'MPS'
ORDER BY MPS_ID DESC)
WHERE RID <= 6) A,
(SELECT MPS_ID, YEAR || MONTH AS MONTH FROM SCM_MPS_QTY) B
WHERE A.MPS_ID = B.MPS_ID;SELECT DISTINCT A.MPS_ID, B.MONTH
FROM (SELECT MPS_ID
FROM (SELECT ROWNUM RID, MPS_ID
FROM SMICSCMADM.SCM_MPS_MAST
WHERE DEL_FLAG = 'N'
AND TYPE = 'MPS'
ORDER BY MPS_ID DESC)
WHERE RID <= 6) A,
(SELECT MPS_ID, YEAR || MONTH AS MONTH FROM SCM_MPS_QTY) B
WHERE A.MPS_ID = B.MPS_ID
当执行Code1 时候表里有数据。
当执行Code2 时候表里没数据。
都不能得到正确的结果.
scm_mps_mast表结构
mps_id
1
2
3
4
5
6
7
...scm_mps_qty
mps_id month
1 201201
1 201202
...
2 201201
2 201202
...想得到的结果是最大的6个mps_id 和 month对应关系.
mps_id month
6 201201
6 201202
...
1 201201
1 201202
...本来想直接从scm_mps_qty中取的,不知道怎么写,就想通过mast表来约束的.
是的,现在的结果一样.但是其中一个子查询.
(SELECT MPS_ID
FROM (SELECT ROWNUM RID, MPS_ID
FROM SMICSCMADM.SCM_MPS_MAST
WHERE DEL_FLAG = 'N'
AND TYPE = 'MPS'
ORDER BY MPS_ID DESC)
WHERE RID <= 6) A
这段是返回最新的6的mps_id吧? 不过结果验证是不正确的:这段sql执行结果是:
MPS_ID
299
297
296
295
294
293
而正确的结果是:
MPS_ID
347
346
345
344
343
342
2 SELECT 347 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
3 SELECT 346 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
4 SELECT 345 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
5 SELECT 344 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
6 SELECT 343 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
7 SELECT 342 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
8 SELECT 341 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
9 SELECT 299 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
10 SELECT 297 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
11 SELECT 296 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
12 SELECT 295 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
13 SELECT 294 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual
14 )SELECT MPS_ID
15 FROM (SELECT ROWNUM RID, MPS_ID
16 FROM SCM_MPS_MAST
17 WHERE DEL_FLAG = 'N'
18 AND TYPE = 'MPS'
19 ORDER BY MPS_ID DESC)
20 WHERE RID <= 6
21 ; MPS_ID
----------
347
346
345
344
343
342已选择6行。[SYS@myoracle] SQL>WITH SCM_MPS_MAST AS(
2 SELECT 347 MPS_ID,'Y' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
3 SELECT 346 MPS_ID,'Y' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
4 SELECT 345 MPS_ID,'Y' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
5 SELECT 344 MPS_ID,'Y' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
6 SELECT 343 MPS_ID,'Y' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
7 SELECT 342 MPS_ID,'Y' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
8 SELECT 341 MPS_ID,'Y' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
9 SELECT 299 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
10 SELECT 297 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
11 SELECT 296 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
12 SELECT 295 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
13 SELECT 294 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual
14 )SELECT MPS_ID
15 FROM (SELECT ROWNUM RID, MPS_ID
16 FROM SCM_MPS_MAST
17 WHERE DEL_FLAG = 'N'
18 AND TYPE = 'MPS'
19 ORDER BY MPS_ID DESC)
20 WHERE RID <= 6
21 ; MPS_ID
----------
299
297
296
295
294[SYS@myoracle] SQL>
SELECT 123 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
SELECT 347 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
SELECT 346 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
SELECT 345 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
SELECT 344 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
SELECT 343 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
SELECT 342 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
SELECT 341 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
SELECT 299 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
SELECT 297 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
SELECT 296 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALL
SELECT 295 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual UNION ALl
SELECT 294 MPS_ID,'N' DEL_FLAG,'MPS' TYPE FROM dual
)SELECT MPS_ID
FROM (SELECT ROWNUM RID, MPS_ID
FROM SCM_MPS_MAST
WHERE DEL_FLAG = 'N'
AND TYPE = 'MPS'
ORDER BY MPS_ID DESC)
WHERE RID <= 6;MPS_ID
----------
347
346
345
344
343
123SCM_MPS_MAST 这张表里的数据不是有序的.
SELECT A.MPS_ID, B.MONTH
FROM (SELECT MPS_ID
FROM (SELECT MPS_ID
FROM SMICSCMADM.SCM_MPS_MAST
WHERE DEL_FLAG = 'N'
AND TYPE = 'MPS'
ORDER BY MPS_ID DESC)
WHERE ROWNUM <= 6) A,
(SELECT MPS_ID, YEAR || MONTH AS MONTH FROM SCM_MPS_QTY) B
WHERE A.MPS_ID = B.MPS_ID;
搞定了,写了个和你的差不多,你的加上distinct也ok了,感谢.SELECT a.mps_id, b.MONTH
FROM (SELECT mps_id
FROM smicscmadm.scm_mps_mast
WHERE del_flag = 'N' AND TYPE = 'MPS' AND ROWNUM <= 6
ORDER BY mps_id DESC) a,
(SELECT DISTINCT mps_id, YEAR || MONTH AS MONTH
FROM smicscmadm.scm_mps_qty) b
WHERE a.mps_id = b.mps_id