汇总函数不错,补充下分析函数 --分析函数 SELECT A FROM (SELECT T.A, T.B, ROW_NUMBER() OVER(PARTITION BY T.B ORDER BY TO_NUMBER(T.A) DESC) RN FROM TEST1 T) WHERE RN = 1
create table test01(a int,b int);insert into test01 values(3,12); insert into test01 values(5,12); insert into test01 values(6,12); insert into test01 values(8,12); insert into test01 values(3,13); insert into test01 values(6,13); insert into test01 values(9,13); insert into test01 values(3,14); insert into test01 values(8,14); insert into test01 values(12,14); commit; --聚合函数 select max(a) from test01 group by b; --分析函数 SELECT A FROM (SELECT T.A, T.B, ROW_NUMBER() OVER(PARTITION BY T.B ORDER BY TO_NUMBER(T.A) DESC) RN FROM TEST01 T) WHERE RN = 1;
SQL> select max(a) as a,b from test group by b order by b; A B ---------- --------------------------------------- 8 12 9 13 12 14
select max(a) as a,b from test group by b order by b;
select max(a),b from t group by b
--分析函数
SELECT A
FROM (SELECT T.A,
T.B,
ROW_NUMBER() OVER(PARTITION BY T.B ORDER BY TO_NUMBER(T.A) DESC) RN
FROM TEST1 T)
WHERE RN = 1
create table test01(a int,b int);insert into test01 values(3,12);
insert into test01 values(5,12);
insert into test01 values(6,12);
insert into test01 values(8,12);
insert into test01 values(3,13);
insert into test01 values(6,13);
insert into test01 values(9,13);
insert into test01 values(3,14);
insert into test01 values(8,14);
insert into test01 values(12,14);
commit;
--聚合函数
select max(a) from test01 group by b;
--分析函数
SELECT A
FROM (SELECT T.A,
T.B,
ROW_NUMBER() OVER(PARTITION BY T.B ORDER BY TO_NUMBER(T.A) DESC) RN
FROM TEST01 T)
WHERE RN = 1;
SQL> select max(a) as a,b from test group by b order by b; A B
---------- ---------------------------------------
8 12
9 13
12 14