员工，部门，打卡3张表，查询每个部门员工打卡最早的打卡记录

分组聚合，Oracle和SQL server的关键字，Mysql8.0以上支持

三张表left join 串起来，然后用加上row_number() over(partition by 部门id order by 打卡时间 desc) rankNumber , 条件where上 这个字段rankNumber=1

  1. select a.* from 
  2. (select *,row_number() over (partition by dept order by date) as rankNumber from emp)
  3. as a where a.rankNumber = 1;
  4. -- a.rankNumber <= 2 前两名

Mysql自增序列号

Mysql中（@i:=@i+1）的作用

:= 和 = 运算符在大部分场景下并无区别，但 := 更为全场景些。 = 只有在 set 和update时才是和 := 一样，赋值的作用，其它都是关系运算符 等于 的作用。 := 不只在 set 和 update 时赋值的作用，在 select 也是赋值的作用。 @用来标识用户变量 @@系统变量

  1. select (@i:=@i+1) as rownum,t.* from (表名) t,(select @i:=0) r;
  1. --根据某一字段，分组，排序号
  3. select 
  4. -- rownum  判断 @now_region_id是否和当前的region_id一样,true:让 @i+=1   false:重置@i
  5. (@i := case  when  @now_region_id=region_id then @i + 1 else 1 end )  rownum,  
  6. --  设置 @now_region_id等于region_id
  7. (@now_region_id:=region_id),  
  8. b.*,
  9. from table b, 
  10. (SELECT @i := 0, @now_region_id:='') as a
  11. order  by  region_id desc
  14. select (@r:= case when @config_id = a.config_id then @r+1 else 1 end) as rowNum,
  15.        (@config_id:=a.config_id),a.*
  16.     from notify_send_record a,(select @r:=0,@config_id:='') b order by a.create_time desc;

分组获取前三条记录

mysql 分组获取前三条记录

  1. CREATE TABLE `employee` (
  2.     `id` int(11) NOT NULL AUTO_INCREMENT,
  3.     `name` varchar(255),
  4.     `salary` decimal(10,2),
  5.     `department_id` int(11),
  6.     PRIMARY KEY (`id`)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  8. -------------------------------------------------------
  9. CREATE TABLE `department` (
  10.     `id` int(11) NOT NULL AUTO_INCREMENT,
  11.     `name` varchar(255),
  12.     PRIMARY KEY (`id`)
  13. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  14. --------------------------------------------------------
  15. 员工表和部门表数据：
  16. INSERT INTO `employee`(`id`, `name`, `salary`, `department_id`) VALUES (1, 'Joe', 70000.00, 1);
  17. INSERT INTO `employee`(`id`, `name`, `salary`, `department_id`) VALUES (2, 'Henry', 80000.00, 2);
  18. INSERT INTO `employee`(`id`, `name`, `salary`, `department_id`) VALUES (3, 'Sam', 60000.00, 2);
  19. INSERT INTO `employee`(`id`, `name`, `salary`, `department_id`) VALUES (4, 'Max', 90000.00, 1);
  20. INSERT INTO `employee`(`id`, `name`, `salary`, `department_id`) VALUES (5, 'Janet', 69000.00, 1);
  21. INSERT INTO `employee`(`id`, `name`, `salary`, `department_id`) VALUES (6, 'Randy', 85000.00, 1);
  22. INSERT INTO `employee`(`id`, `name`, `salary`, `department_id`) VALUES (7, 'Eva', 85000.00, 1);
  23. INSERT INTO `department`(`id`, `name`) VALUES (1, 'IT');
  24. INSERT INTO `department`(`id`, `name`) VALUES (2, 'Sales');
  1. select n.*
  2. from (
  3.      select (@r := case when @user_id = a.id then @r + 1 else 1 end) as rowNum,
  4.             (@user_id := a.id),
  5.             a.*
  6.      from employee a,
  7.           (select @r := 0, @user_id := '') b
  8.       left join department d on a.department_id = d.id
  9.      order by a.salary asc
  10.      ) n
  11. where n.rowNum < 4;
  1. SELECT d.`name` AS '部门',e.`name` AS '员工',e.salary AS '工资' FROM employee e
  2. JOIN department d ON d.id = e.department_id
  3. WHERE
  4.   (  SELECT count(DISTINCT em.salary) FROM employee em 
  5.      WHERE em.salary > e.salary AND em.department_id = e.department_id
  6.   ) < 3
  7. ORDER BY e.department_id, e.salary DESC
  9. 输出结果如下：
  10. 部门    员工    工资
  11. IT    Max    90000
  12. IT    Randy    85000
  13. IT    Eva    85000
  14. IT    Joe    70000
  15. Sales    Henry    80000
  16. Sales    Sam    60000
  17. 首先来理解一下上面的 SQL，当 < 3 的条件改为 = 0 时，即子表中相同部门没有比主表工资高的员工，
  18. 则取得工资最高的员工；
  19. 当条件为 = 1 时，表示子表中相同部门里只有一个比主表工资高的员工，则取得工资第二高的员工；
  20. 同理，条件 = 2 表示工资第三高的员工，所以工资前三高的员工的条件为 < 3。
  21. 通过结果可以看到，第二名员工和第三名员工工资相同，被当作并列第二，并不会排挤掉第三名。如果我们希望出现并列第二名时，第三名就变成第四名呢？
  22. 可以把 count(DISTINCT em.salary) 改成 count(*)。

参考

MySQL实战45讲
《面试笔记》——MySQL终结篇（30问与答）