sql取每组前N行数据

mysql环境

表的信息结构

mysql> select * from test02;
+------+------+-----+
| id   | name | num |
+------+------+-----+
|    1 | a4   |   4 |
|    1 | a1   |   2 |
|    1 | a2   |   2 |
|    1 | a3   |   1 |
|    2 | b1   |   3 |
|    2 | b2   |   2 |
|    2 | b3   |   1 |
|    3 | b3   |   3 |
|    3 | b2   |   2 |
|    3 | b1   |   1 |
|    4 | c1   |   1 |
+------+------+-----+
11 rows in set (0.00 sec)

mysql> select * from test02 a where exists (select count(*) from test02 where id=a.id and num>a.num having count(*)<2);
+------+------+-----+
| id   | name | num |
+------+------+-----+
|    1 | a4   |   4 |
|    1 | a1   |   2 |
|    1 | a2   |   2 |
|    2 | b1   |   3 |
|    2 | b2   |   2 |
|    3 | b3   |   3 |
|    3 | b2   |   2 |
|    4 | c1   |   1 |
+------+------+-----+
8 rows in set (0.00 sec)

num相等的的也取出了

 

 ------每组取前3行--num相等不相等都取前3行

mysql> select a.* from

(select (@rownum:=@rownum+1) AS dxid,bb.* from (select id,name,num from test02 order by id,num desc) bb,(SELECT @rownum:=0) b) a where exists (select count(*) from (select (@rownum:=@rownum+1) AS dxid,bb.* from (select id,name,num from test02 order by id,num desc) bb,(SELECT @rownum:=0) b) c where c.id=a.id and c.dxid<a.dxid having count(*)<3);
+------+------+------+-----+
| dxid | id   | name | num |
+------+------+------+-----+
|    1 |    1 | a4   |   4 |
|    2 |    1 | a1   |   2 |
|    3 |    1 | a2   |   2 |
|    5 |    2 | b1   |   3 |
|    6 |    2 | b2   |   2 |
|    7 |    2 | b3   |   1 |
|    8 |    3 | b3   |   3 |
|    9 |    3 | b2   |   2 |
|   10 |    3 | b1   |   1 |
|   11 |    4 | c1   |   1 |
+------+------+------+-----+
10 rows in set (0.00 sec)

 

 -----------每组取前2行

mysql> select a.* from (select (@rownum:=@rownum+1) AS dxid,bb.* from (select id,name,num from test02 order by id,num desc) bb,(SELECT @rownum:=0) b) a where exists (select count(*) from (select (@rownum:=@rownum+1) AS dxid,bb.* from (select id,name,num from test02 order by id,num desc) bb,(SELECT @rownum:=0) b) c where c.id=a.id and c.dxid<a.dxid having count(*)<2);
+------+------+------+-----+
| dxid | id   | name | num |
+------+------+------+-----+
|    1 |    1 | a4   |   4 |
|    2 |    1 | a1   |   2 |
|    5 |    2 | b1   |   3 |
|    6 |    2 | b2   |   2 |
|    8 |    3 | b3   |   3 |
|    9 |    3 | b2   |   2 |
|   11 |    4 | c1   |   1 |
+------+------+------+-----+
7 rows in set (0.01 sec)

mysql>

 

----------------------------------------------------------------------------------------------------------

 

select * from ( select (t1.rownum- t2.minnum) as num, t1.id, t1.name from (select @rownum:=@rownum+1 as rownum, test02.id,test02.name from (select @rownum:=0) r, test02 order by test02.id) t1 join (select min(rownum) as minnum, id from (select @rownum:=@rownum+1 as rownum, test02.id from (select @rownum:=0) r, test02 order by test02.id) t1 group by id) t2 on t1.id = t2.id) t3 where num between 0 and 1;

 -------------------------------------------------------------------

oracle环境

SQL> SELECT deptno, ename,
  2         ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC) seq
  3    FROM emp;

    DEPTNO ENAME             SEQ
---------- ---------- ----------
        10 KING                1
        10 CLARK               2
        10 MILLER              3
        20 SCOTT               1
        20 FORD                2
        20 JONES               3
        20 ADAMS               4
        20 SMITH               5
        30 BLAKE               1
        30 ALLEN               2
        30 TURNER              3
        30 WARD                4
        30 MARTIN              5
        30 JAMES               6

14 rows selected.

---------------------------------------------------------------------------------

SQL> select deptno,
  2  max(decode(seq,1,ename,null)) highest,
  3  max(decode(seq,2,ename,null)) second,
  4  max(decode(seq,3,ename,null)) third
  5  from (
  6  select deptno,ename,
  7  row_number() over
  8  (partition by deptno order by sal desc) seq
  9  from emp)
 10  where seq <=3 group by deptno
 11  /

    DEPTNO HIGHEST    SECOND     THIRD
---------- ---------- ---------- ----------
        10 KING       CLARK      MILLER
        20 SCOTT      FORD       JONES
        30 BLAKE      ALLEN      TURNER

 --------------------------------------------------------------------------------

sqlserver2005环境

select deptno,ename,
row_number() over
(partition by deptno order by sal desc) seq
from emp

与oracle基本相同

 select deptno,
max(case seq when 1 then ename else null end) highest,
max(case seq when 2 then ename else null end) second,
max(case seq when 3 then ename else null end) third
from (
select deptno,ename,
row_number() over
(partition by deptno order by sal desc) seq
from emp) a
where a.seq <=3 group by a.deptno