與oracle的rownumber() over(partition by xxxorder by xxx)語句類似，即：對表分組后排序

創(chuàng)建測試emp表

DROP TABLE IF EXISTS emp;

CREATE TABLE emp (

empno decimal(4,0) NOT NULL,

ename varchar(10) DEFAULT NULL,

job varchar(9) DEFAULT NULL,

mgr decimal(4,0) DEFAULT NULL,

hiredate datetime DEFAULT NULL,

sal decimal(7,2) DEFAULT NULL,

comm decimal(7,2) DEFAULT NULL,

deptno decimal(2,0) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

-- Records of emp

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

INSERT INTO emp VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17 00:00:00', '800.00', null, '20');

INSERT INTO emp VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20 00:00:00', '1600.00', '300.00', '30');

INSERT INTO emp VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22 00:00:00', '1250.00', '500.00', '30');

INSERT INTO emp VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02 00:00:00', '2975.00', null, '20');

INSERT INTO emp VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28 00:00:00', '1250.00', '1400.00', '30');

INSERT INTO emp VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01 00:00:00', '2850.00', null, '30');

INSERT INTO emp VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09 00:00:00', '2450.00', null, '10');

INSERT INTO emp VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1982-12-09 00:00:00', '3000.00', null, '20');

INSERT INTO emp VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17 00:00:00', '5000.00', null, '10');

INSERT INTO emp VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08 00:00:00', '1500.00', '0.00', '30');

INSERT INTO emp VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1983-01-12 00:00:00', '1100.00', null, '20');

INSERT INTO emp VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03 00:00:00', '950.00', null, '30');

INSERT INTO emp VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03 00:00:00', '3000.00', null, '20');

INSERT INTO emp VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23 00:00:00', '1300.00', null, '10');

需求：按部門分組后顯示工資為前三的員工信息

SQL：

select * from emp as a where 3> (select count(*) from emp where deptno = a.deptno and sal < a.sal ) order by a.deptno ,a.sal

解析SQL：

3>...

目的是獲取前三條數(shù)據(jù)，可以多次修改

emp where deptno = a.deptno

目的是確定分組字段

sal < a.sal

排序條件

結(jié)果圖：

總結(jié)

以上是生活随笔為你收集整理的mysql分组取出每组地一条数据_MySQL 分组后取每组前N条数据的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。