sql语句如下select *
from (select t.*,
row_number() over(partition by t.id order by t.major) as rn
from (select ps.id id,
ps.fk_fee_level_id fee,
ps.fk_grade_id grade,
pecs.fk_major_id major,
pecs.credit credit,
pecs.code code
from pe_student ps, pe_edutype_credit_semster pecs
where pecs.fk_edutype_id = ps.fk_edutype_id
and pecs.fk_major_id = ps.fk_major_id
union
select ps.id id,
ps.fk_fee_level_id fee,
ps.fk_grade_id grade,
pecs.fk_major_id major,
pecs.credit credit,
pecs.code code
from pe_student ps, pe_edutype_credit_semster pecs
where pecs.fk_edutype_id = ps.fk_edutype_id
and pecs.fk_major_id is null) t)
where rn = 1
uinon的一个是全匹配的一个是指匹配一项另一项为空的
然后把为空匹配和全匹配中重复的去全匹配的最多有两条
from (select t.*,
row_number() over(partition by t.id order by t.major) as rn
from (select ps.id id,
ps.fk_fee_level_id fee,
ps.fk_grade_id grade,
pecs.fk_major_id major,
pecs.credit credit,
pecs.code code
from pe_student ps, pe_edutype_credit_semster pecs
where pecs.fk_edutype_id = ps.fk_edutype_id
and pecs.fk_major_id = ps.fk_major_id
union
select ps.id id,
ps.fk_fee_level_id fee,
ps.fk_grade_id grade,
pecs.fk_major_id major,
pecs.credit credit,
pecs.code code
from pe_student ps, pe_edutype_credit_semster pecs
where pecs.fk_edutype_id = ps.fk_edutype_id
and pecs.fk_major_id is null) t)
where rn = 1
uinon的一个是全匹配的一个是指匹配一项另一项为空的
然后把为空匹配和全匹配中重复的去全匹配的最多有两条
ps.fk_fee_level_id fee,
ps.fk_grade_id grade,
pecs.fk_major_id major,
pecs.credit credit,
pecs.code code
from pe_student ps, pe_edutype_credit_semster pecs
where (pecs.fk_edutype_id = ps.fk_edutype_id
and pecs.fk_major_id = ps.fk_major_id)
or pecs.fk_major_id is null--你这个前面部分都是相同的
)
--试试左外连接之后再赛选
select id,fee,grade,major,credit,code
from (
select ps.id id,
ps.fk_fee_level_id fee,
ps.fk_grade_id grade,
pecs.fk_major_id major,
pecs.credit credit,
pecs.code code,
ps.fk_edutype_id tid
from pe_student ps left outer join pe_edutype_credit_semster pecs
on pecs.fk_edutype_id = ps.fk_edutype_id
) t
WHERE EXISTS( SELECT 1 FROM pe_edutype_credit_semster WHERE fk_edutype_id=t.tid)
select *
(select ps.id id,
ps.fk_fee_level_id fee,
ps.fk_grade_id grade,
pecs.fk_major_id major,
pecs.credit credit,
pecs.code code,
row_number() over(partition by ps.id order by pecs.fk_major_id ) as rn
from pe_student ps, pe_edutype_credit_semster pecs
where pecs.fk_edutype_id = ps.fk_edutype_id
and (pecs.fk_major_id = ps.fk_major_id or pecs.fk_major_id is null)) t
where t.rn=1