--------- 建表
CREATE TABLE TABLE_A
(A DATE,
B INTEGER,
C VARCHAR2(10),
D VARCHAR2(10)
)
--------- 测试数据
INSERT INTO TABLE_A VALUES(TO_DATE('2001-1-1','YYYY-MM-DD'),'123','张三','备注');
INSERT INTO TABLE_A VALUES(TO_DATE('2001-1-1','YYYY-MM-DD'),'123','李四','备注');
INSERT INTO TABLE_A VALUES(TO_DATE('2002-1-1','YYYY-MM-DD'),'123','张三','备注');
INSERT INTO TABLE_A VALUES(TO_DATE('2003-1-1','YYYY-MM-DD'),'123','李四','备注');
COMMIT;
--------- SQL
SELECT A,
B,
C,
D
FROM (SELECT TABLE_A.*,
ROW_NUMBER() OVER(PARTITION BY C ORDER BY A DESC) AS R
FROM TABLE_A)
WHERE R <= 1 ----- 可以用 R<= N来确定取前N条记录
---------- 结果
A B C D
2003-1-1 123 李四 备注
2002-1-1 123 张三 备注
几点说明:
上面的sql语句中用到了内嵌查询select from select的形式:
select * from (select column_name from table_name )
row_number() over()函数的用法,over主要是给出条件。
PARTITION 为分类
ORDER为排序