字段A 字段B 字段C 字段D
1 1 aaa bbb
2 1 ds ds
3 1 ff ff
4 2 33 33
5 2 44 44
6 3 hh hh
7 3 hh hh
8 3 tt tt要求结果:
字段A 字段B 字段C 字段D
3 1 ff ff
5 2 44 44
8 3 tt tt即:按照B字段分组,取每组中的A字段最大值的记录
1 1 aaa bbb
2 1 ds ds
3 1 ff ff
4 2 33 33
5 2 44 44
6 3 hh hh
7 3 hh hh
8 3 tt tt要求结果:
字段A 字段B 字段C 字段D
3 1 ff ff
5 2 44 44
8 3 tt tt即:按照B字段分组,取每组中的A字段最大值的记录
from table
where (字段B,字段A) in (select 字段B,max(字段A) from table group by 字段B)
CREATE TABLE table1(
A NUMBER,
B NUMBER,
C VARCHAR(10),
D VARCHAR(10)
)INSERT INTO table1
VALUES(1,1,'aaa','bbb');INSERT INTO table1
VALUES(2,1,'ds','ds');INSERT INTO table1
VALUES(3,1,'ff','ff');INSERT INTO table1
VALUES(4,2,'33','33')INSERT INTO table1
VALUES(5,2,'44','44');INSERT INTO table1
VALUES(6,3,'hh','hh');INSERT INTO table1
VALUES(7,3,'hh','hh');INSERT INTO table1
VALUES(8,3,'tt','tt')SELECT *
FROM table1
WHERE A IN (SELECT A FROM (SELECT MAX(A) A, B FROM table1 GROUP BY B))结果为:1 3 1 ff ff
2 5 2 44 44
3 8 3 tt ttDROP TABLE table1
字段A 字段B 字段C 字段D
1 1 aaa bbb
2 1 ds ds
3 1 ff ff
1 2 33 33
2 2 44 44
6 3 hh hh
7 3 hh hh
8 3 tt tt 严格意义上来讲,我认为ling242a的语句写的好
从lz给的数据看,字段A应该是主键
zxf1@ORCL(192.168.21.110)> select * from table1; A B C D
---------- ---------- ---------- ----------
1 1 aaa bbb
2 1 ds ds
3 1 ff ff
5 2 44 44
6 3 hh hh
7 3 hh hh
8 3 tt tt已选择7行。已用时间: 00: 00: 00.03
zxf1@ORCL(192.168.21.110)> select a,b,c,d
2 from (select a,b,c,d,row_number() over(partition by b order by a desc) recno from table1)
3 where recno=1; A B C D
---------- ---------- ---------- ----------
3 1 ff ff
5 2 44 44
8 3 tt tt已用时间: 00: 00: 00.03
2 union
3 select 2 a,1 b, 'ds' c, 'ds' d from dual
4 union
5 select 3 a,1 b, 'ff' c, 'ff' d from dual
6 union
7 select 4 a,2 b, '33' c, '33' d from dual
8 union
9 select 5 a,2 b, '44' c, '44' d from dual
10 union
11 select 6 a,3 b, 'hh' c, 'hh' d from dual
12 union
13 select 7 a,3 b, 'hh' c, 'hh' d from dual
14 union
15 select 8 a,3 b, 'tt' c, 'tt' d from dual
16 )
17 select a,b,c,d from (select max(a)keep(dense_rank first order by a desc)over(partition by b) a1
,a, b,c,d
18 from a)
19 where a1=a
20 / A B C D
---------- ---------- --- ---
3 1 ff ff
5 2 44 44
8 3 tt tt