在公司内部的一个项目中有这样一个需求:
两张表:
A
id name
1 x
2 y
3 x
4 z
B id a.id b.name
1 1 hhh
2 9 yyy
要查询按name不重复的,并且b的a.id在a中能找到,查出所有的a的name不一样的记录
select person.*,a.* from (
select row_number() over(partition by t.N_PSCYXH order by t.n_xh DESC) r
,t.* from t_htgl_htpsjl t where t.n_compactid={req.htbh}) a ,t_htgl_person person
where person.n_id=a.N_PSCYXH(+) and person.n_compactid={req.htbh} and (a.r is null or a.r<2) order by a.N_XH DESC
两张表:
A
id name
1 x
2 y
3 x
4 z
B id a.id b.name
1 1 hhh
2 9 yyy
要查询按name不重复的,并且b的a.id在a中能找到,查出所有的a的name不一样的记录
select person.*,a.* from (
select row_number() over(partition by t.N_PSCYXH order by t.n_xh DESC) r
,t.* from t_htgl_htpsjl t where t.n_compactid={req.htbh}) a ,t_htgl_person person
where person.n_id=a.N_PSCYXH(+) and person.n_compactid={req.htbh} and (a.r is null or a.r<2) order by a.N_XH DESC