解决方案 »
- distinct 取消重复行 是什么意思
- 求生成xml的方法
- 救命 ORA-00942: 表或视图不存在
- [提问]请问loop的结束判定是怎么确定的?
- oracle 10g 本地客户端 可以连接远程 oracle 9i吗
- 触发器能否对插入数据库表异常的字段,进行记录,并将异常写到表中!
- 奇怪的问题,设置varchar2(10)只能3个中文字加一个字母,再多就出错了,插入的值过长?
- 请问各位高手怎么把如下这个SQL改为一个select语句
- oralce联合子查询的疑惑
- 新人提问,plsql无法登录oraclexe数据库的问题??
- 在webservice服务端添加拦截器
- 新手 低级问题 无法标注注释
尝试吧select A,max(c) from table group by A 单独建一张临时表,子查询实在影响效能,然后再和主表关联!
select a.* from table a where a.C = (Select Max(c)
From table b
Where a.A = b.A)
SELECT '张三' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130411 C FROM dual
UNION
SELECT '张三' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130511 C FROM dual
UNION
SELECT '张三' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130311 C FROM dual
UNION
SELECT '李四' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130611 C FROM dual
UNION
SELECT '李四' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130311 C FROM dual
UNION
SELECT '李四' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130411 C FROM dual
UNION
SELECT '王五' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130311 C FROM dual
UNION
SELECT '王五' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20140211 C FROM dual
)
SELECT *
FROM c_test t
WHERE EXISTS (SELECT 1
FROM (SELECT t.a,MAX(t.c) c
FROM c_test t
GROUP BY t.a) b
WHERE b.a = t.a
AND t.c = b.c);
from a t
where not exists(select 1 from a where a=t.a and c>t.c);
7楼结果应该是不正确的
例:按照ADB分组
结果中
张三 65 73
张三 66 74
张三 69 77
在结果中应该都会出现,而按楼主的要求,应该只要最新的一条
select a,d,b,max(c) from table group by a
row_number() over(parititon by ..... order by .... desc)
select * from
(select a.*,row_number() over(partition by a.a order by a.c desc) rn)
where rn=1;
from q t
where not exists(select 1 from q where A=t.A and C>t.C);
SELECT '张三' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130411 C FROM dual
UNION
SELECT '张三' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130511 C FROM dual
UNION
SELECT '张三' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130311 C FROM dual
UNION
SELECT '李四' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130611 C FROM dual
UNION
SELECT '李四' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130311 C FROM dual
UNION
SELECT '李四' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130411 C FROM dual
UNION
SELECT '王五' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130311 C FROM dual
UNION
SELECT '王五' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20140211 C FROM dual
)
select a.* from c_test a,
(select s.a name,max(c) maxtime from c_test s group by s.a) b
where a.A=b.name and a.C=b.maxtime;