详见如下。
[@more@]语句1:SELECT row_.*, ROWNUM rownum_ FROM ( SELECT (CASE WHEN MAT.MAT_TYPE = 'SLAB' THEN 2 WHEN MAT.MAT_TYPE != 'SLAB' THEN 3 END) AS PRINT_COUNT, si.SHIP_ID AS SHIP_ID, si.SHIP_TYPE AS SHIP_TYPE, si.TRACK_ID AS TRACK_ID, si.SHIP_DATETIME AS SHIP_DATETIME, mat.SO_ID AS SO_ID, mat.SO_ITEM_ID AS SO_ITEM_ID, 1 AS PRINT_SUM, (SELECT MAX (sop.PROPERTY_VALUE) FROM MES_ORD_SALE_ORDER_PROP sop WHERE sop.SO_ID = mat.SO_ID AND SOP.ITEM_NO = MAT.SO_ITEM_ID AND sop.PROPERTY_NAME = 'CERT_TYPE') AS PROPERTY_VALUE FROM (SELECT * FROM MES_WTM_SHIP_ITEM s WHERE s.sid = (SELECT MAX (n.sid) FROM MES_WTM_SHIP_ITEM n WHERE n.ship_id = s.ship_id)) si, MES_MAT_MATERIAL mat WHERE mat.MAT_ID = si.MAT_ID AND si.SHIP_ID IS NOT NULL AND TO_CHAR (si.SHIP_DATETIME, 'yyyy-MM-dd') >= '2010-07-12' AND TO_CHAR (si.SHIP_DATETIME, 'yyyy-MM-dd') <= '2010-07-12' AND mat.MAT_TYPE IN ('HRC', 'HRCS') ORDER BY si.SHIP_DATETIME DESC , si.SHIP_ID DESC ) row_
执行查询后,没有数据。如果将最下面的 order by si.SHIP_DATETIME DESC , si.SHIP_ID DESC 改为默认升序排序,那么执行查询后,显示有147行数据。对比执行计划发现,由于语句1的最外层添加了rownum,导致在执行查询的时候内部子查询被单独执行,即先执行了第一个查询,最后再执行子查询。这样由于第一个查询没有引用关联字段sid,所以在执行子查询的时候找不到对应字段,所以也就没有正确显示数据
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25702/viewspace-1035205/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25702/viewspace-1035205/