sql取每个分组的第一行数据

CREATE TABLE table1(a VARCHAR(10),b VARCHAR(10),c VARCHAR(10));

--²åÈëÊý¾Ý

INSERT INTO table1 VALUES('2004','12','storea');

INSERT INTO table1 VALUES('2005','07','storea');

INSERT INTO table1 VALUES('2004','11','storeb');

INSERT INTO table1 VALUES('2005','06','storeb');

COMMIT;

2 È¡·Ö×é¼Ç¼µÄµÚÒ»Ìõ

½â·¨Ò»

SELECT a.* FROM table1 a INNER JOIN ( SELECT MAX(a + b)TIME FROM table1 GROUP BY c ) b

ON a.a + a.b = b.TIME

½â·¨¶þ

SELECT * FROM scott.table1 a WHERE NOT EXISTS

(SELECT 1 FROM scott.table1 b WHERE a.a=b.a TO_NUMBER(a.b)½â·¨Èý

SELECT * FROM (SELECT t.*, RANK()

OVER (PARTITION BY t.a ORDER BY t.b DESC) AS drank FROM table1 t) a WHERE drank=1

ÒýÉê

SELECT t.*,SUM(b) OVER (PARTITION BY t.a) AS aaa, SUM(b) OVER () AS bbb FROM table1 t ORDER BY t.a,t.b

AND

£-£-£-£-£-£-

SELECT d.department_id , e.last_name, e.salary, RANK()

OVER (PARTITION BY e.department_id ORDER BY e.salary) AS drank FROM employees e, departments d

WHERE e.department_id = d.department_id AND d.department_id IN ('60', '90');