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>
----------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------
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