select t.name as 姓名,
t.sex as 性别,
t.serialcode as 档案号,
t.identitycard as 身份证号,
t.nation as 民族,
t.maritalstatus as 婚姻状况,
t.education as 文化程度,
t.job as 职业,
t1.smokenumber
from hr_records t
right join hr_dismgtdia_followup t1
on t.identitycard = t1.identitycard
join
(select max(id) as maxid, b.identitycard
from hr_dismgtdia_followup b
group by b.identitycard) o
on maxid = t1.id
where t.companynumber = '19'
and INSTR(CHRONICID, '3') > 0
order by t.id查出来条数少于 hr_records 这个表在t.companynumber = '19'
and INSTR(CHRONICID, '3') > 0
这两个条件下条数oracle外连接
t.sex as 性别,
t.serialcode as 档案号,
t.identitycard as 身份证号,
t.nation as 民族,
t.maritalstatus as 婚姻状况,
t.education as 文化程度,
t.job as 职业,
t1.smokenumber
from hr_records t
right join hr_dismgtdia_followup t1
on t.identitycard = t1.identitycard
join
(select max(id) as maxid, b.identitycard
from hr_dismgtdia_followup b
group by b.identitycard) o
on maxid = t1.id
where t.companynumber = '19'
and INSTR(CHRONICID, '3') > 0
order by t.id查出来条数少于 hr_records 这个表在t.companynumber = '19'
and INSTR(CHRONICID, '3') > 0
这两个条件下条数oracle外连接
join
(select max(id) as maxid, b.identitycard
from hr_dismgtdia_followup b
group by b.identitycard) o
on maxid = t1.id
on t.identitycard = t1.identitycard
join
(select max(id) as maxid, b.identitycard
from hr_dismgtdia_followup b
group by b.identitycard) o
on maxid = t1.id改为
(select b.identitycard ,row_number() over(partiton by b.identitycard order by id) rn from hr_dismgtdia_followup b) t1
on t.identitycard =t1.identitycard
and rn=1
and INSTR(CHRONICID, '3') > 0
这两个条件下条数
---------------------------
那我觉得你right join应该改为left join
t.sex as 性别,
t.serialcode as 档案号,
t.identitycard as 身份证号,
t.nation as 民族,
t.maritalstatus as 婚姻状况,
t.education as 文化程度,
t.job as 职业,
t1.smokenumber
from hr_records t
LEFT join (select b.identitycard,
row_number()over(partiton by b.identitycard order by id) rn
from hr_dismgtdia_followup b) t1
on t.identitycard = t1.identitycard
and rn = 1
where t.companynumber = '19'
and INSTR(CHRONICID, '3') > 0
order by t.id这样子..