oracle排序问题

Question

 select *
   from (select rownum as rn, ab.*
           from (SELECT t.id,
                        e.ehr_id,
                        nh_code,
                        name,
                        sex,
                        id_card,
                        (to_number(to_char(sysdate, 'yyyy')) -
                        to_number(to_char(birthday, 'yyyy'))) age,
                        home_address,
                        telephone,
                        t.enable_flag,
                        t.create_user,
                        t.create_date,
                        t.modify_date,
                        village_id,
                        (SELECT answer
                           FROM ehr_s_person_answer
                          WHERE ehr_id = e.ehr_id
                            AND question_id = 546) lxrdh,
                        (SELECT count(1)
                           FROM ehr_examination_record
                          WHERE ehr_id = e.ehr_id
                            AND enable_flag = 1) exam_count,
                        (SELECT count(1)
                           FROM ehr_ht_manage_visit_record
                          WHERE ehr_id = e.ehr_id
                            AND enable_flag = 1) visit_count
                   FROM ehr_info e
                   JOIN ehr_hypertension t
                     ON e.ehr_id = t.ehr_id
                    AND t.enable_flag != 3
                  WHERE  e.region_id IN (23)
                    AND e.hosp_id = 1
                  order by sex desc) ab
          where rownum <= 40) abc
  where rn > 30有时候第三，第四页出来的数据是一样呢？？？？红色里面查出来的序列是唯一的啊各位有什么解决方案？？？

Answer 1

t.enable_flag != 3 这个条件一般做不作为表连接的条件吧？据经验，应该放在where后进行过滤

Answer 2

你这经验有点吓人啊
join 放在这里有什么关系

Answer 3

呵呵 t.enable_flag != 3 这个是过滤条件 on 后面是连接条件 这是常识啊  哥哥 你要是左连接的话  t.enable_flag != 3这个基本没有用了

Answer 4

我们分页就是你这样写的。。没问题啊可是根据order by sex desc排序有时候出来的结果不一致啊

Answer 5

我们分页就是你这样写的。。没问题啊有时候
  
 select *
   from (select rownum as rn, ab.*
           from (SELECT t.id,
                        e.ehr_id,
                        nh_code,
                        name,
                        sex,
                        id_card,
                        (to_number(to_char(sysdate, 'yyyy')) -
                        to_number(to_char(birthday, 'yyyy'))) age,home_address,
                        telephone,
                        t.enable_flag,
                        t.create_user,
                        t.create_date,
                        t.modify_date,
                        village_id,
                        (SELECT answer
                           FROM ehr_s_person_answer
                          WHERE ehr_id = e.ehr_id
                            AND question_id = 546) lxrdh,
                        (SELECT count(1)
                           FROM ehr_examination_record
                          WHERE ehr_id = e.ehr_id
                            AND enable_flag = 1) exam_count,
                        (SELECT count(1)
                           FROM ehr_ht_manage_visit_record
                          WHERE ehr_id = e.ehr_id
                            AND enable_flag = 1) visit_count
                   FROM ehr_info e
                   JOIN ehr_hypertension t
                     ON e.ehr_id = t.ehr_id
                    AND t.enable_flag != 3
                  WHERE  e.region_id IN (23)
                    AND e.hosp_id = 1
                  order by sex desc) ab
          where rownum <= 50) abc
  where rn > 40
出来的数据和上面的一致

Answer 6

-- 如果每页10条的话
select * from (
SELECT row_number() over(order by sex desc) rn,
       t.id,
       e.ehr_id,
       nh_code,
       name,
       sex,
       id_card,
       (to_number(to_char(sysdate, 'yyyy')) -
       to_number(to_char(birthday, 'yyyy'))) age,
       home_address,
       telephone,
       t.enable_flag,
       t.create_user,
       t.create_date,
       t.modify_date,
       village_id
       -- 这几个独立子查询自己补上
  FROM ehr_info e
  JOIN ehr_hypertension t
    ON e.ehr_id = t.ehr_id
 WHERE e.region_id IN (23)
   AND e.hosp_id = 1
   AND t.enable_flag != 3)
 where rn between 31 and 40;