解决方案 »
- 在Redhat AS4上基于ASM存储的ORACLE 10g单实例数据库安装
- 两列同是同一个表的外键,怎么样关联查询
- 请教:存储过程中如何定义表变量?
- OIP-04109: Error creating temporary file是什么原因呢
- oracle11g怎么在web页上插数据啊
- 目前做oracle + shell批处理,以后能往什么方向发展
- oracle中都什么样的字段可以做sum等数字操作
- 到底ORACLE的Variable Size是怎么计算得到的。
- 求助:用Pro*C 在 VC 中访问Oracle编译错误的问题
- 提供程序不支持带有 RECORD 或 TABLE 变量的 PL/SQL 存储过程/函数???
- oracle9i中的isqlplus中如何设置输出选项?
- SOS:关于分区表批量提交的问题
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;
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)