mysql分组后获取组内数据

 

1.数据库表

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;

2.插入的数据

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');

3.查询语句

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

4.大体思路

解析SQL：

4>...

目的是获取前三条数据，可以多次修改

emp where deptno = a.deptno

目的是确定分组字段

sal < a.sal

排序条件