解决方案 »
- 求条统计成绩的sql
- 查询中有DBlink的性能问题
- Oracle基于表创建新表
- 数据库的用户问题,有哪几类用户?
- oracle10 XMLDOM写入XML文件,报错 目录路径无效 。请求援助。。。
- oracle触发器中,为什么在这段代码会报异常呢?。。。。。。。。。。。。。。。
- 如何从表中取出唯一值??
- oracle中没有没与sql server中osql.exe类似的命令行程序,不是指sqlplus,在线等
- 如何得到某个用户的默认表空间和临时表空间????
- 关于oracle对中文字段的排序的问题!
- java连接oracle jdbc错误
- hibernate执行sql语句出现“Caused by: java.sql.SQLException: ORA-00911: 无效字符”
(
select 'a' name, 'b' code1, 'e' code2, 7 num from dual
union all
select 'a','b','d',3 from dual
union all
select 'a','c','c',4 from dual
union all
select 'a','c','f',2 from dual
union all
select 'b','d','f',3 from dual
union all
select 'b','d','e',5 from dual)
select name, code1,max(code2),max(num) from tab group by name,code1 order by name,code1
-----------------------------------
name code1 max(code2) max(num)
a b e 7
a c f 4
b d f 5
select 'a' name, 't' code1, 't' code2, 3 num from dual
union all
select 'a','b','e',7 from dual
union all
select 'a','b','d',3 from dual
union all
select 'a','n','s',4 from dual
union all
select 'a','c','c',4 from dual
union all
select 'a','c','f',7 from dual
union all
select 'b','d','f',3 from dual
union all
select 'b','d','e',5 from dual
)
select name, code1, code2, num from(
select name, code1, code2, num,row_number()over(partition by name,code1 order by name,code1, num desc) rn
from tab
)
where rn=1
order by name, code1NAME CODE1 CODE2 NUM
----------------------------
a b e 7
a c f 7
a n s 4
a t t 3
b d e 5
1 with tb as (
2 select 'a' name, 't' code1, 't' code2, 3 num from dual
3 union all
4 select 'a','b','e',7 from dual
5 union all
6 select 'a','b','d',3 from dual
7 union all
8 select 'a','n','s',4 from dual
9 union all
10 select 'a','c','c',4 from dual
11 union all
12 select 'a','c','f',7 from dual
13 union all
14 select 'b','b','f',3 from dual
15 union all
16 select 'b','b','e',5 from dual
17 )
18 select * from tb t where not exists (
19* select * from tb where t.name=name and t.code1=code1 and t.num<num)
SQL> /N C C NUM
- - - ----------
a c f 7
b b e 5
a t t 3
a b e 7
a n s 4
(
select 'a' name, 't' code1, 't' code2, 3 num from dual
union all
select 'a','b','e',7 from dual
union all
select 'a','b','d',3 from dual
union all
select 'a','n','s',4 FROM dual
union all
select 'a','c','c',4 from dual
union all
select 'a','c','f',7 from dual
union all
select 'b','b','f',3 from dual
union all
select 'b','b','e',5 from dual
)select a.name,a.code1,a.code2,b.maxnum FROM data_tabname a,(select name,code1,max(num) maxnum FROM data_tabname
group by name,code1) b
where a.name = b.name and a.code1 = b.code1 and a.num = b.maxnum
order by a.name,a.code1;data_tabname就是你的数据表。
--再一看,吓我一跳,变了这么多~~~
--改成分析函数就行了,用max的话就是忽略code2,嘿嘿~
with tab as
(
select 'a' name, 't' code1, 't' code2, 3 num from dual
union all
select 'a' name, 'b' code1, 'e' code2, 7 num from dual
union all
select 'a','b','d',3 from dual
union all
select 'a','n','s',4 from dual
union all
select 'a','c','c',4 from dual
union all
select 'a','c','f',7 from dual
union all
select 'b','b','f',3 from dual
union all
select 'b','b','e',5 from dual)select name,code1,code2,num from (
select name,code1,code2,num, row_number() over(partition by name,code1 order by name, code1,num desc) row_num from tab
) where row_num <=1
-------------------------------
name code1 code2 num
a b e 7
a c f 7
a n s 4
a t t 3
b b e 5