17,140
社区成员




select w.A, w.B, w.C, w.E, w.D
from foo w, (select A, B, C, max(E) as F from foo group by A, B, C) t
where w.A = t.A
and w.B = t.B
and w.C = t.C
and E = t.F
order by w.A, w.B, w.C
查询结果:
11 22 33 2 B
22 22 33 1 B
33 22 33 3 A
44 22 33 1 A
55 22 33 1 Z
CREATE TABLE foo(
a VARCHAR2(20),
b VARCHAR2(20),
c VARCHAR2(20),
d VARCHAR2(20),
e VARCHAR2(20)
);
INSERT INTO foo VALUES ('11','22','33','A','0');
INSERT INTO foo VALUES ('11','22','33','B','2');
INSERT INTO foo VALUES ('11','22','33','C','1');
INSERT INTO FOO VALUES ('11','22','33','A','0');
INSERT INTO FOO VALUES ('11','22','33','B','2');
INSERT INTO FOO VALUES ('11','22','33','C','1');
INSERT INTO FOO VALUES ('22','22','33','D','0');
INSERT INTO FOO VALUES ('22','22','33','B','1');
INSERT INTO FOO VALUES ('33','22','33','C','2');
INSERT INTO FOO VALUES ('33','22','33','A','3');
INSERT INTO FOO VALUES ('44','22','33','K','0');
INSERT INTO FOO VALUES ('44','22','33','A','1');
INSERT INTO FOO VALUES ('55','22','33','Z','1');
查询SQL:
SELECT O.A,O.B,O.C,O.D,O.E FROM
(SELECT a,b,c,d,e,MAX(e) over(PARTITION BY a,b,c) AS eg FROM foo) o
WHERE o.eg =e;
查询结果:
A B C D E
11 22 33 B 2
11 22 33 B 2
22 22 33 B 1
33 22 33 A 3
44 22 33 A 1
55 22 33 Z 1
select w.A, w.B, w.C, w.E, w.D
from test2 w, (select A, B, C, max(D) as F from test2 group by A, B, C) t
where w.A = t.A
and w.B = t.B
and w.C = t.C
and D = t.F
order by w.A, w.B, w.C