select * from csdn where factoryid = 11102 union select customerid,customername,factoryid from (select * from csdn a where customerid not in (select distinct customerid from csdn where factoryid=11102) order by factoryid desc) where rownum<= (select count(distinct(customerid)) from csdn a where customerid not in (select distinct customerid from csdn where factoryid=11102));
select * from cus_factory where id in( --当@factoryid 不等于 facotryid 时 取factoryid为大的记录 select max(id)keep(dense_rank first order by factoryid desc) from cus_factory group by customerid having customerid not in (select distinct customerid from cus_factory where factoryid=@factoryid) union --带入@factoryid为参数 同一customerid有且只有一条记录 select min(id)keep(dense_rank first order by factoryid desc) from cus_factory where factoryid=@factoryid group by customerid )
select * from csdn where factoryid = 11102
union
select customerid,customername,factoryid from (select * from csdn a where
customerid not in
(select distinct customerid from csdn where factoryid=11102) order by factoryid desc) where rownum<= (select count(distinct(customerid)) from csdn a where customerid not in (select distinct customerid from csdn where factoryid=11102));
--当@factoryid 不等于 facotryid 时 取factoryid为大的记录
select max(id)keep(dense_rank first order by factoryid desc)
from cus_factory
group by customerid
having customerid not in (select distinct customerid from cus_factory where factoryid=@factoryid)
union
--带入@factoryid为参数 同一customerid有且只有一条记录
select min(id)keep(dense_rank first order by factoryid desc)
from cus_factory where factoryid=@factoryid group by customerid
)