表A 结构如下
ID NUM1 NUM2 NUM3 DATETIME
1 1 12 23 2011-12-15
2 1 12 34 2011-12-13
3 1 12 34 2011-12-18
4 1 13 45 2011-12-18
5 1 13 45 2011-12-15 想查出所有不重复的num2,及当时的id,num1,num3和最后出现的时间
最终返回如下结果:ID NUM1 NUM2 NUM3 DATETIME
1 1 12 23 2011-12-15
3 1 12 34 2011-12-18
4 1 13 45 2011-12-18
ID NUM1 NUM2 NUM3 DATETIME
1 1 12 23 2011-12-15
2 1 12 34 2011-12-13
3 1 12 34 2011-12-18
4 1 13 45 2011-12-18
5 1 13 45 2011-12-15 想查出所有不重复的num2,及当时的id,num1,num3和最后出现的时间
最终返回如下结果:ID NUM1 NUM2 NUM3 DATETIME
1 1 12 23 2011-12-15
3 1 12 34 2011-12-18
4 1 13 45 2011-12-18
解决方案 »
- Oracle中Blob类型存储的是备注信息(汉字),现在需要用关键字过滤备注从而达到对表中数据的过滤,怎么做呢?
- windows server 2003 重启之后 oracle无法启动 问题
- 请教:用export向导作导出工作时,作业状态为失败
- 紧急求助:使用C#编写的程序,本地oracle9客户端,访问远程oracle7数据库的问题!
- pro*c编程 sql语句的问题
- 这几句plsql中的%s是什么意思
- 自动共享内存管理
- 怎样导入导出blob类型的数据?
- oracle怎么创建方案?
- 请问如何可以知道应用程序都对哪些表进行的操作?
- 求这句sql优化,时间太长了用8秒多,请高人具体指点
- 我的机器是win7 64位,现在想要安装一个oracle9可是网上找不到资源啊
(selct NUM2,max(DATETIME) as DATETIME from 表A group by NUM2) viewA
where 表A.NUM2=viewA.NUM2 and 表A.DATETIME =viewA.DATETIME
-- 看你的答案是不重复的NUM3吧?偶猜你NUM2是写错了
SELECT *
FROM A
WHERE (NUM3,DATETIME) IN (SELECT NUM3,MAX(DATETIME) FROM A GROUP BY NUME);
SELECT *FROM A
WHERE ROWID!=(SELECT MAX(ROWID)FROM A B WHERE B.NUM2=A.NUM2 );
select ID, NUM1,NUM2,NUM3,DATETIME,
row_number() over(partition by NUM3 order by DATETIME desc) rn
from A
) where rn=1
select 1 As id ,1 as num1 ,12 as num2,23 as num3,to_date('2011-12-15','yyyy-mm-dd') as datetime from dual
union all
select 2 As id ,1 as num1 ,12 as num2,34 as num3, to_date('2011-12-13','yyyy-mm-dd') as datetime from dual
union all
select 3 As id ,1 as num1 ,12 as num2,34 as num3, to_date('2011-12-18','yyyy-mm-dd') as datetime from dual
union all
select 4 As id ,1 as num1 ,13 as num2,45 as num3, to_date('2011-12-18','yyyy-mm-dd') as datetime from dual
union all
select 5 As id ,1 as num1 ,13 as num2,45 as num3, to_date('2011-12-15','yyyy-mm-dd') as datetime from dual
)
select id,num1,num2,num3,datetime from
( select a.*,row_number() over( partition by num3 order by datetime desc ) rn from a ) where rn =1
你这个应该是 所有num3不重复的哟。