这是多张表查询的结果,现在想根据sumdyp值将qu字段去重,并且其它几列都要显示。
select tt.*,
(select t4.addvnm
from st_addvcd_d_loc t4
where tt.addvcd = t4.addvcd
group by t4.addvnm) qu
from (select t.addvcd, t.stnm, sum(t.dyp) sumdyp
from (select t1.stcd, t1.dyp, t2.stnm, t2.addvcd, t3.addvnm
from st_pptn_r_loc t1,
st_stbprp_b_loc t2,
st_addvcd_d_loc t3
where dyp is not null
and t1.stcd = t2.stcd
and t2.addvcd = t3.addvcd) t
group by t.addvcd, t.stnm
order by sumdyp desc) tt
select tt.*,
(select t4.addvnm
from st_addvcd_d_loc t4
where tt.addvcd = t4.addvcd
group by t4.addvnm) qu
from (select t.addvcd, t.stnm, sum(t.dyp) sumdyp
from (select t1.stcd, t1.dyp, t2.stnm, t2.addvcd, t3.addvnm
from st_pptn_r_loc t1,
st_stbprp_b_loc t2,
st_addvcd_d_loc t3
where dyp is not null
and t1.stcd = t2.stcd
and t2.addvcd = t3.addvcd) t
group by t.addvcd, t.stnm
order by sumdyp desc) tt
from (select addvcd,
stnm,
sumdyp,
qu,
row_number() over(partition by qu order by sumdyp desc ) rn
from 表名)
where rn = 1