这个表是以name1,zs desc排序的,如何取出每个分组的第一条记录(黄色背景)
解决方案 »
- toad的schema browser设置
- OCP认证怎么样?有个老师给我说,OCP保过,让我去考,但是要银子
- 在线急等--oralce客户端连不上服务器问题
- oracle中关于列名不显示的问题
- 临时表问题
- oracle的逻辑判断(在表不存在期望的数据的判断)
- 请教利用oracle 10g的EM来修改和插入表中数据的问题
- 大家知道怎么设置server端使客户端访问时也可以as sysdba啊?
- 存储过程返回记录集该怎么写啊?在线等待!
- 100分的问题, 连接sqlplus 成功,连接 DBArtisan 错误.错误内容附上.
- oracle 临时变量赋值问题
- PLS-00703: 列表中具有指定参数的多个实例
WITH table1 AS
(
SELECT 'aa' AS name1, 'a1' AS name2,'30' AS zs FROM dual
union all
SELECT 'aa' AS name1, 'a2' AS name2,'5' AS zs FROM dual
union all
SELECT 'aa' AS name1, 'a3' AS name2,'3' AS zs FROM dual
union all
SELECT 'aa' AS name1, 'a4' AS name2,'3' AS zs FROM dual
union all
SELECT 'bb' AS name1, 'b1' AS name2,'20' AS zs FROM dual
union all
SELECT 'bb' AS name1, 'b2' AS name2,'11' AS zs FROM dual
union all
SELECT 'cc' AS name1, 'c1' AS name2,'30' AS zs FROM dual
union all
SELECT 'cc' AS name1, 'c2' AS name2,'30' AS zs FROM dual
union all
SELECT 'dd' AS name1, 'd' AS name2,'40' AS zs FROM dual
union all
SELECT 'dd' AS name1, 'd1' AS name2,'25' AS zs FROM dual
union all
SELECT 'dd' AS name1, 'd2' AS name2,'20' AS zs FROM dual
union all
SELECT 'dd' AS name1, 'd3' AS name2,'0' AS zs FROM dual
)
select name1,name2,zs from (
select row_number()over(partition by name1 order by name2,zs desc) as rn ,t.* from table1 t) where rn = 1
from t_talbe
where (name1,zs) in (
select t.name1,max(t.zs)
from t_table t
group by t.name1);
select *
from table1 t1
where not exists (select 1
from table1 t2
where t2.name1 = t1.name1
and t2.zs > t1.zs) order by t1.name1;
NAME1 NAME2 ZS
----- ----- --
aa a2 5
bb b1 20
cc c2 30
cc c1 30
dd d 40cc有两条都是33的,应该是取两条吧?要不然你随便取其中一条?
SELECT 'aa' AS name1, 'a1' AS name2, 30 AS zs
FROM dual
union all
SELECT 'aa' AS name1, 'a2' AS name2, 5 AS zs
FROM dual
union all
SELECT 'aa' AS name1, 'a3' AS name2, 3 AS zs
FROM dual
union all
SELECT 'aa' AS name1, 'a4' AS name2, 3 AS zs
FROM dual
union all
SELECT 'bb' AS name1, 'b1' AS name2, 20 AS zs
FROM dual
union all
SELECT 'bb' AS name1, 'b2' AS name2, 11 AS zs
FROM dual
union all
SELECT 'cc' AS name1, 'c1' AS name2, 30 AS zs
FROM dual
union all
SELECT 'cc' AS name1, 'c2' AS name2, 30 AS zs
FROM dual
union all
SELECT 'dd' AS name1, 'd' AS name2, 40 AS zs
FROM dual
union all
SELECT 'dd' AS name1, 'd1' AS name2, 25 AS zs
FROM dual
union all
SELECT 'dd' AS name1, 'd2' AS name2, 20 AS zs
FROM dual
union all
SELECT 'dd' AS name1, 'd3' AS name2, 0 AS zs
FROM dual
)
select *
from table1 t1
where not exists (select 1
from table1 t2
where t2.name1 = t1.name1
and t2.zs > t1.zs) order by t1.name1;NAME1 NAME2 ZS
----- ----- ----------
aa a1 30
bb b1 20
cc c2 30
cc c1 30
dd d 40