男人oracle,有趣的分组函数

分组函数
记得有道sql 面试题是。以emp表为例
找出每个部门薪水的前三名。

SELECT *
  FROM (SELECT A.*, ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY JOB) RN
          FROM EMP A)
 WHERE RN <= 3;

 
这是一个分组函数的简单应用。
昨天公司要开年会，分房，标准间。两个人一间。
同事又不好说“我要和你一房”。。呵呵
来写个随机分组吧。
以emp为例。。14个人。分为7组。

SELECT B.*, MOD(ROWNUM, 7) ROOM
  FROM (SELECT A.* FROM EMP A ORDER BY DBMS_RANDOM.RANDOM) B
 ORDER BY ROOM;

 
很简单吗。呵呵。这样分完，同事可乐坏了。男女混住。哈哈
要改，一定要改
为了测试 给emp 加个sex 字段

CREATE TABLE my_emp AS SELECT * FROM emp;

ALTER TABLE my_emp ADD  sex VARCHAR2(1);

UPDATE my_emp SET sex=MOD(empno,2);

 

这回要男女分开，有利精神文明建设。哈哈

SELECT A.*,
       sex || CEIL(RANK() OVER(PARTITION BY A.SEX ORDER BY ROWNUM)/2) AS ROOM_ID
FROM (SELECT * FROM my_emp ORDER BY DBMS_RANDOM.random) A;

 
我想到了over 却没想到ceil 这是itpub szusunny 兄台写的。不错不错。

 

ENAME      SEX ROOM_ID
---------- --- -----------------------------------------
MARTIN     0   01
MILLER     0   01
ADAMS      0   02
TURNER     0   02
BLAKE      0   03
JONES      0   03
SCOTT      0   04
FORD       0   04
JAMES      0   05
CLARK      0   05
SMITH      1   11
ALLEN      1   11
WARD       1   12
KING       1   12

 

顺便囧一下