mysql如何在一对多查询时选取时间最近的一条记录

懒散的作者
2020-11-07 / 0 评论 / 634 阅读 / 正在检测是否收录...
需求是:
查询员工id,部门名称,入职时间,部门编号,部门名称,如果在一个部门中查到同一个员工有两条入职记录,显示最近的一条。
1.前提准备,建立两张表,部门表和员工表
CREATE TABLE `dept` (
  `deptId` int(11) NOT NULL,
  `deptNum` int(11) DEFAULT NULL,
  `deptName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`deptId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
CREATE TABLE `emp` (
  `empId` int(11) NOT NULL,
  `empName` varchar(255) DEFAULT NULL,
  `empCreateTime` date DEFAULT NULL,
  `deptNum` int(11) DEFAULT NULL,
  PRIMARY KEY (`empId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
INSERT INTO `dept` VALUES (1, 10, '开发部');
INSERT INTO `dept` VALUES (2, 20, '测试部');
INSERT INTO `dept` VALUES (3, 30, '财务部');
 
INSERT INTO `emp` VALUES (1, '张晓华', '2019-4-2', 10);
INSERT INTO `emp` VALUES (2, '张晓华', '2019-4-13',10);
INSERT INTO `emp` VALUES (3, '王大锤', '2019-3-1', 20);
INSERT INTO `emp` VALUES (4, '王大锤', '2019-4-5', 20);
INSERT INTO `emp` VALUES (5, '王霸丹', '2019-2-1', 30);
INSERT INTO `emp` VALUES (6, '王霸丹', '2019-2-21',30);

首先先关联查一下看看 结果

SELECT
    A.empId,
    A.empName,
    A.empCreateTime,
    A.deptNum,
    B.deptId,
    B.deptName
FROM emp A
INNER join dept B
ON A.deptNum =  B.deptNum

从结果中可以看出部门与员工的一对多关系,我们先来试一下按照部门号分组

SELECT
    A.empId,
    A.empName,
    A.empCreateTime,
    A.deptNum,
    B.deptName
FROM emp A
INNER join dept B
ON A.deptNum =  B.deptNum
GROUP BY A.deptNum

跟上次对比

   发现虽然,虽然按照deptNum分了三个组,但是 比较悲催的是,分组后默认选取的是每个组中的第一条数据。而默认查询结果又是按照升序排列的,所以他选取的时间值是最小值,但离我们最近的日期值应该是最大值,所以结果并不符合我们的要求。


那么增么办才能让分组后第一条数据是时间的最大值呢?

给出解决思路先关联查询,然后按时间降序排列,这样我们可以得到一张表


SELECT 

    A.empId,A.empName,A.empCreateTime, A.deptNum,B.deptName

FROM emp A

INNER join dept B

ON A.deptNum = B.deptNum

ORDERY by A.empCreateTime DESC

太好了 ,再按照deptNum分组第一条数据就是我们想要的数据了

额。。。。。。报错了,mysql不能排序之后在分组,但是分组之后就已经去重了,再排序有个毛用。

仿佛进入了死胡同。。。。。。

解决办法:
我们可以先排序,排序之后把这个结果当成一个临时表,然后再从这个表中查询结果,最后再排序就可以完美解决。

例子:

方法以下几种:

先查询表几条demo数据,名字相同,时间不同


select id,name,create_date from sys_user 

方法1 : (最简单,且字段全部相同,排除其他字段不同 )
先对表按照时间desc排序,在查询该层使用group by 语句,它会按照分组将你排过序的数据的第一条取出来
select id,name,create_date from ( select * from sys_user order by create_date  desc) a group by a.name 
注意:5.7以后对排序的sql解析做了优化,子查询中的排序是会被忽略的,

5.6你这样写是没问题的,5.7的话需要换一换了
使用聚合函数取出你要的记录再关联原表获取第一条记录
或者使用组内排序生成行号后再按行号取第一条也行。

换个写法:

/**子查询临时表中添加limit  99999 ,或者用第2种方法**/
select id,name,create_date from ( select * from sys_user order by create_date  desc `limit 9999`) a group by a.name

使用limit的话解析就会不一样了,相当于临时表取值范围发生了变化,如果是全表的话,优化器就忽略掉了


方法2 : (使用内关联的方式 )
找出最大的时间或者最大的id 进行 自表=自表 ,根据name分组,
select * from sys_user a

      inner join (

        -- 先查询出最后一条数据的时间

        select id,name, `MAX(create_date) create_date` from sys_user `group by name`

      ) b on a.name = b.name and `a.create_date = b.create_date`  

方法3 : (使用not exists,该方法通过相同名字的不同创建的时间进行比较 )
select id,name,create_date from sys_user a

where `not exists`  (select * from sys_user b where `a.name = b.name` and `a.create_date < create_date )  ;

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/ZQQ8015/article/details/89321790

0

评论 (0)

取消