sqlservergroupby分组后，取每个分组的第⼀条数据
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  INTO  table1 VALUES('2005','06','storeb');    COMMIT;
groupby分组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 * 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
－－－－－－
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');

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。