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有时候第三,第四页出来的数据是一样呢????红色里面查出来的序列是唯一的啊各位有什么解决方案???
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有时候第三,第四页出来的数据是一样呢????红色里面查出来的序列是唯一的啊各位有什么解决方案???
join 放在这里有什么关系
我们分页就是你这样写的。。没问题啊可是根据order by sex desc排序有时候出来的结果不一致啊
我们分页就是你这样写的。。没问题啊有时候
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
出来的数据和上面的一致
-- 如果每页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;