DXID DXLXID COMPID
1 5069 1300
2 5069 1300
3 5069 1300
4 5069 1400
5 5069 1500
6 5069 1600
7 5069 1700
8 5069 1800
6 5069 1200
6 5069 1500
8 5069 1700
如上数据有什么方法挑选出，DXID,和DXLXID，对应多个COMPID的列，然后再挑选出对应了一个COMPID的列呢？
SELECT ROW_NUMBER() OVER(PARTITION BY dxid,dxlxid ORDER BY compid DESC) compid_count,
dxid,dxlxid,compid from mytest
这个可以出求出如下结果，可惜，怎么分离出来呢？
1 1 5069 1300
1 2 5069 1300
1 3 5069 1300
1 4 5069 1400
1 5 5069 1500
1 6 5069 1600
2 6 5069 1500
3 6 5069 1200
1 7 5069 1700
1 8 5069 1800
2 8 5069 1700
------解决方案--------------------
在上面的基础上进行分组统计，选取统计结果为1的那条记录便是：
WITH A AS (SELECT ROW_NUMBER() OVER(PARTITION BY dxid, dxlxid ORDER BY compid DESC) AS compid_count,
dxid,
dxlxid,
compid
FROM mytest)
SELECT A.compid_count, A.dxid, A.dxlxid, A.compid
FROM A,
(SELECT compid_count, COUNT(*) AS num
FROM A
GROUP BY compid_count) B
WHERE A.compid_count = B.compid_count
AND B.num = 1;
------解决方案--------------------
with mytest as(
select 1 DXID,5069 DXLXID,1300 COMPID from dual union all
select 2 DXID,5069 DXLXID,1300 COMPID from dual union all
select 3 DXID,5069 DXLXID,1300 COMPID from dual union all
select 4 DXID,5069 DXLXID,1400 COMPID from dual union all
select 5 DXID,5069 DXLXID,1500 COMPID from dual union all
select 6 DXID,5069 DXLXID,1600 COMPID from dual union all
select 7 DXID,5069 DXLXID,1700 COMPID from dual union all
select 8 DXID,5069 DXLXID,1800 COMPID from dual union all
select 6 DXID,5069 DXLXID,1200 COMPID from dual union all
select 6 DXID,5069 DXLXID,1500 COMPID from dual union all
select 8 DXID,5069 DXLXID,1700 COMPID from dual )
select *
from mytest
where dxid
------解决方案--------------------
dxlxid in (SELECT dxid
------解决方案--------------------
dxlxid
from mytest
group by dxid, dxlxid
having count(1) = 1)