《sql取每个分组的第一行数据》由会员分享，可在线阅读，更多相关《sql取每个分组的第一行数据（2页珍藏版）》请在金锄头文库上搜索。
1、sql取每个分组的第一行数据.txt35温馨是大自然的一抹色彩，独具慧眼的匠师才能把它表现得尽善尽美；温馨是乐谱上的一个跳动音符，感情细腻的歌唱者才能把它表达得至真至纯1 -建立测试环境 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 I
2、NTO 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 AND TO_NUMBER(a.b)TO_NUMBER(b.b);解法三SELECT
3、 * FROM (SELECT t.*, RANK() OVER (PARTITION BY t.a ORDER BY t.b DESC) AS drankFROM table1 t) a WHERE drank=1引申SELECT t.*,SUM(b) OVER (PARTITION BY t.a) AS aaa, SUM(b) OVER () AS bbbFROM table1 tORDER BY t.a,t.bSELECT d.department_id , e.last_name, e.salary, RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary) AS drankFROM employees e, departments dWHERE e.department_id = d.department_id AND d.department_id IN (60, 90);
