语句如下,取出来的结果是时间条件内的最大值和最值,等于说rownum 是无效的。
(rownum肯定是大于500的),有些什么方法使用rownum有效?SELECT max(to_number(f.offset_x)) AS maxoffset_x,
min(to_number(f.offset_x)) AS minoffset_x,
max(to_number(f.offset_y)) AS maxoffset_y,
min(to_number(f.offset_y)) AS minoffset_y,
max(to_number(f.scale_x)) AS maxscale_x,
min(to_number(f.scale_x)) AS minscale_x,
max(to_number(f.scale_y)) AS maxscale_y,
min(to_number(f.scale_y)) AS minscale_y,
max(to_number(f.rotation)) AS maxrotation,
min(to_number(f.rotation)) AS minrotation,
max(to_number(f.orthogonality)) AS maxorthogonality,
min(to_number(f.orthogonality)) AS minorthogonality,
max(to_number(f.shot_scale_x)) AS maxshot_scale_x,
min(to_number(f.shot_scale_x)) AS minshot_scale_x,
max(to_number(f.shot_scale_y)) AS maxshot_scale_y,
min(to_number(f.shot_scale_y)) AS minshot_scale_y,
max(to_number(f.shot_scale_avr)) AS maxshot_scale_avr,
min(to_number(f.shot_scale_avr)) AS minshot_scale_avr,
max(to_number(f.shot_rotation)) AS maxshot_rotation,
min(to_number(f.shot_rotation)) AS minshot_rotation,
max(to_number(f.cx_min)) AS maxcx_min,
min(to_number(f.cx_min)) AS mincx_min,
max(to_number(f.cx_max)) AS maxcx_max,
min(to_number(f.cx_max)) AS mincx_max,
max(to_number(f.cy_min)) AS maxcy_min,
min(to_number(f.cy_min)) AS mincy_min,
max(to_number(f.cy_max)) AS maxcy_max,
min(to_number(f.cy_max)) AS mincy_max,
max(to_number(f.mx_min)) AS maxmx_min,
min(to_number(f.mx_min)) AS minmx_min,
max(to_number(f.mx_max)) AS maxmx_max,
min(to_number(f.mx_max)) AS minmx_max,
max(to_number(f.my_min)) AS maxmy_min,
min(to_number(f.my_min)) AS minmy_min,
max(to_number(f.my_max)) AS maxmy_max,
min(to_number(f.my_max)) AS minmy_max
FROM (SELECT DISTINCT s.LOT_RRN, e.lot_id
FROM DATA_COLLECTION c, DCOL_STEP_INFO s, lot e
WHERE c.DCOL_RRN = s.DCOL_RRN
AND e.lot_rrn = s.lot_rrn
AND PARAMETER_SET_RRN = 4567414
AND PARAMETER_SET_VERSION = 1 ) A
INNER JOIN overy_lay_value f ON A.lot_id = f.LOT_ID
WHERE f.CREATE_TIME >= '2013-02-15'
AND F.CREATE_TIME <= '2013-03-16'
AND Rownum <= 500
结果
(rownum肯定是大于500的),有些什么方法使用rownum有效?SELECT max(to_number(f.offset_x)) AS maxoffset_x,
min(to_number(f.offset_x)) AS minoffset_x,
max(to_number(f.offset_y)) AS maxoffset_y,
min(to_number(f.offset_y)) AS minoffset_y,
max(to_number(f.scale_x)) AS maxscale_x,
min(to_number(f.scale_x)) AS minscale_x,
max(to_number(f.scale_y)) AS maxscale_y,
min(to_number(f.scale_y)) AS minscale_y,
max(to_number(f.rotation)) AS maxrotation,
min(to_number(f.rotation)) AS minrotation,
max(to_number(f.orthogonality)) AS maxorthogonality,
min(to_number(f.orthogonality)) AS minorthogonality,
max(to_number(f.shot_scale_x)) AS maxshot_scale_x,
min(to_number(f.shot_scale_x)) AS minshot_scale_x,
max(to_number(f.shot_scale_y)) AS maxshot_scale_y,
min(to_number(f.shot_scale_y)) AS minshot_scale_y,
max(to_number(f.shot_scale_avr)) AS maxshot_scale_avr,
min(to_number(f.shot_scale_avr)) AS minshot_scale_avr,
max(to_number(f.shot_rotation)) AS maxshot_rotation,
min(to_number(f.shot_rotation)) AS minshot_rotation,
max(to_number(f.cx_min)) AS maxcx_min,
min(to_number(f.cx_min)) AS mincx_min,
max(to_number(f.cx_max)) AS maxcx_max,
min(to_number(f.cx_max)) AS mincx_max,
max(to_number(f.cy_min)) AS maxcy_min,
min(to_number(f.cy_min)) AS mincy_min,
max(to_number(f.cy_max)) AS maxcy_max,
min(to_number(f.cy_max)) AS mincy_max,
max(to_number(f.mx_min)) AS maxmx_min,
min(to_number(f.mx_min)) AS minmx_min,
max(to_number(f.mx_max)) AS maxmx_max,
min(to_number(f.mx_max)) AS minmx_max,
max(to_number(f.my_min)) AS maxmy_min,
min(to_number(f.my_min)) AS minmy_min,
max(to_number(f.my_max)) AS maxmy_max,
min(to_number(f.my_max)) AS minmy_max
FROM (SELECT DISTINCT s.LOT_RRN, e.lot_id
FROM DATA_COLLECTION c, DCOL_STEP_INFO s, lot e
WHERE c.DCOL_RRN = s.DCOL_RRN
AND e.lot_rrn = s.lot_rrn
AND PARAMETER_SET_RRN = 4567414
AND PARAMETER_SET_VERSION = 1 ) A
INNER JOIN overy_lay_value f ON A.lot_id = f.LOT_ID
WHERE f.CREATE_TIME >= '2013-02-15'
AND F.CREATE_TIME <= '2013-03-16'
AND Rownum <= 500
结果
你这个SQL写的很有意思哦...前面查询的都是聚合函数,例如min avg max sum count 等等,聚合函数得到的是一个值,但是后面又用上了rownum<=500
rownum的。
原因是先rownum再distinct还是先distinct再取rownum的问题原语句与影响的结果如下:SELECT distinct
f.instance_rrn,
s.LOT_RRN,
e.lot_id,
f.lot_id || to_char(f.wafer) AS waferNo,
f.instance_rrn,
f.eqpt_id,
f.wafer_type,
f.result_file,
f.wafer,
f.offset_x,
f.offset_y,
f.scale_x,
f.scale_y,
f.rotation,
f.orthogonality,
f.shot_scale_x,
f.shot_scale_y,
f.shot_scale_avr,
f.shot_rotation,
f.cx_min,
f.cx_max,
f.cy_min,
f.cy_max,
f.mx_min,
f.mx_max,
f.my_min,
f.my_max,
f.create_time
FROM DATA_COLLECTION c,
DCOL_STEP_INFO s,
SAMPLE_DATA d,
lot e,
overy_lay_value f
WHERE c.DCOL_RRN = s.DCOL_RRN
AND c.DCOL_SEQUENCE = d.DCOL_SEQUENCE
AND c.DCOL_RRN = d.DCOL_RRN
and d.comments = 1
AND e.lot_rrn = s.lot_rrn
AND e.lot_id = f.lot_id
AND PARAMETER_SET_RRN = 4567414
AND PARAMETER_SET_VERSION = 1
AND f.CREATE_TIME >= '2013-02-14'
AND F.CREATE_TIME <= '2013-03-15'
and rownum<=500结果:375条方法一,先distinct作为子查询,再联接 SELECT A.*,B.* FROM
(SELECT DISTINCT s.LOT_RRN,
e.lot_id
FROM
DATA_COLLECTION c,
DCOL_STEP_INFO s,
SAMPLE_DATA d,
lot e
WHERE c.DCOL_RRN = s.DCOL_RRN
AND c.DCOL_SEQUENCE = d.DCOL_SEQUENCE
AND c.DCOL_RRN = d.DCOL_RRN
and d.comments = 1
AND e.lot_rrn = s.lot_rrn
AND PARAMETER_SET_RRN = 4567414
AND PARAMETER_SET_VERSION = 1)A
INNER JOIN overy_lay_value B ON A.lot_id=B.LOT_ID
WHERE B.CREATE_TIME >= '2013-02-14'
AND B.CREATE_TIME <= '2013-03-15'
AND rownum<=500
结果:500条第二种,在结果里面查结果select fe.instance_rrn, lot_rrn, fe.lot_id, fe.lot_id || to_char(fe.wafer) AS waferNo, fe.instance_rrn, fe.eqpt_id, fe.wafer_type, fe.result_file, fe.wafer, fe.offset_x, fe.offset_y, fe.scale_x, fe.scale_y, fe.rotation, fe.orthogonality, fe.shot_scale_x, fe.shot_scale_y, fe.shot_scale_avr, fe.shot_rotation, fe.cx_min, fe.cx_max, fe.cy_min, fe.cy_max, fe.mx_min, fe.mx_max, fe.my_min, fe.my_max, fe.create_time from (SELECT DISTINCT f.* ,e.lot_rrn FROM DATA_COLLECTION c, DCOL_STEP_INFO s, SAMPLE_DATA d, lot e, overy_lay_value f WHERE c.DCOL_RRN = s.DCOL_RRN AND c.DCOL_SEQUENCE = d.DCOL_SEQUENCE AND c.DCOL_RRN = d.DCOL_RRN and d.comments = 1 AND e.lot_rrn = s.lot_rrn AND e.lot_id = f.lot_id AND PARAMETER_SET_RRN = 4567414 AND PARAMETER_SET_VERSION = 1 AND f.CREATE_TIME >= '2013-02-14' AND F.CREATE_TIME <= '2013-03-15') fe where Rownum <= 500 结果,也是500条。