• MySQL 分组后取每组前N条数据


    与oracle的 rownumber() over(partition by xxx  order by xxx )语句类似,即:对表分组后排序

    创建测试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>...

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

    emp where deptno = a.deptno

    目的是确定分组字段

    sal < a.sal

    排序条件

    结果图:

      

  • 相关阅读:
    HTML技巧篇:如何让单行文本以及多行文本溢出时显示省略号(…)
    SpringMVC中响应json数据(异步传送)
    如何用Spring框架的<form:form>标签实现REST风格的增删改查操作
    如何使用REST请求风格
    Spring插件的安装与卸载---笔记
    元素 "context:component-scan" 的前缀 "context" 未绑定的解决方案
    简单的文件上传的下载(动态web项目)
    用简单的反射优化代码(动态web项目)
    json数据与Gson工具类的使用
    JSON简介
  • 原文地址:https://www.cnblogs.com/duyunchao-2261/p/7460294.html
Copyright © 2020-2023  润新知