如何分组求出,几列和其中一列的关系只出现过一次的呢
本帖最后由 luoyanqun 于 2014-04-13 23:21:26 编辑
DXID DXLXID COMPID
150691300
250691300
350691300
450691400
550691500
650691600
750691700
850691800
650691200
650691500
850691700
如上数据有什么方法挑选出,DXID,和DXLXID,对应多个COMPID的列,然后再挑选出对应了一个COMPID的列呢?
SELECT ROW_NUMBER() OVER(PARTITION BY dxid,dxlxid ORDER BY compid DESC) compid_count,
dxid,dxlxid,compid from mytest
这个可以出求出如下结果,可惜,怎么分离出来呢?
1150691300
1250691300
1350691300
1450691400
1550691500
1650691600
2650691500
3650691200
1750691700
1850691800
2850691700
------解决方案--------------------
在上面的基础上进行分组统计,选取统计结果为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;
------解决方案--------------------
引用:DXID DXLXID COMPID
150691300
250691300
350691300
450691400
550691500
650691600
750691700
850691800
650691200
650691500
850691700
如上数据有什么方法挑选出,DXID,和DXLXID,对应多个COMPID的列,然后再挑选出对应了一个COMPID的列呢?
SELECT ROW_NUMBER() OVER(PARTITION BY dxid,dxlxid ORDER BY compid DESC) compid_count,
dxid,dxlxid,compid from mytest
这个可以出求出如下结果,可惜,怎么分离出来呢?
1150691300
1250691300
1350691300
1450691400
1550691500
1650691600
2650691500
3650691200
1750691700
1850691800
2850691700
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)