Mysql中获取分组的最新一条记录

stevenwang

软件数学问题爱好者

基本原理

SELECT `客户ID`,max(订购日期) from `订单` GROUP BY `客户ID`;

然后在与原始表进行inner join

Note：原始表中，订购日期要增加一个索引。唯一性约束，否则在join的时候，可能会有多余的记录。因为客户`客户ID`,max(订购日期) ；有可能有多个。在原始表中，与本表进行inner join关联的时候，作为连接Key。会出现笛卡尔集的现象。

FAQ:如果这一个列中，唯一性约束，可能需要数据库中的时间列到秒级。然后在所有的值中，不能重复。这个约束要求有点高。实际上，要求是这个组内的约束不能充分就可以，但是暂时无法处理这个约束。

mysql group by 组内排序方法

MySQL之group by与max()一起使用的坑（这个应该是mysql的高级版本，可以group by获取第一条所有信息）g roup by 分组后显示的是第一条记录，而max()取的是相同sid中的最大score值造成的 ，如下

注意：如果mysql的版本支持 any_value。以下的SQL执行没有问题，但是结果有问题

SELECT
INNER JOIN (
	SELECT
		any_value (`订单ID`) s订单id,
		GROUP_CONCAT(订单ID),
		max(订购日期)
	GROUP BY
		`客户ID`
) s ON `订单`.`订单ID` = s.`s订单id`

会出现在一条记录中，any_value的值，与分组的客户ID不是同一个记录的可能性。所以在应用中不要使用any_value的方法

设计文档

业务要求

然后以填报明细，进行left join 绩效明细 left join 绩效主表, 形成一张表。然后根据上面的理论，获取到每个绩效明细的最新的一条数据。

然后根据这个数据集，进行 select 绩效主表的id,sum（绩效值）from 绩效主表的id group by 绩效主表的id

把这个值，填入到FR中，进行隐藏。然后在FR中，进行判断

如果填报明细中，没有数据。那么在汇总的时候，就是null。然后转为0

判断的逻辑不在sql语句中实现，在上层逻辑，有报表来进行实现。

考核主表中，采用报表工具计算出来主表的得分值。计算逻辑为

选择考核明细表中的最新一条填报明细。然后与主表进行Join。结果表根据主表的考核ID进行join。如果用户没有填写过，那么汇总值为null，转为0.

在报表逻辑计算该值，

如果（该汇总值- 计算参考值）>100

按照100来计算权重

如果 <0

按照0来进行计算

如果在[0,100]区间，那么计算的值计算权重

数据ER关系表

数据关系考核主表 1：n 考核明细表 1：n 填报明细

构造模拟数据

考核主表

考核明细表

考核项目填报明细

在数据库中创建3张表

     /*
Navicat MySQL Data Transfer
Source Server         : 127.0.0.1
Source Server Version : 80015
Source Host           : localhost:3306
Source Database       : fr
Target Server Type    : MYSQL
Target Server Version : 80015
File Encoding         : 65001
Date: 2022-08-20 18:43:53
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for 考核主表
-- ----------------------------
DROP TABLE IF EXISTS `考核主表`;
CREATE TABLE `考核主表` (
  `考核ID` int(11) NOT NULL AUTO_INCREMENT,
  `权重` int(11) DEFAULT NULL,
  `计算参考分数` int(11) DEFAULT NULL,
  `汇总得分` int(11) DEFAULT NULL,
  PRIMARY KEY (`考核ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of 考核主表
-- ----------------------------
INSERT INTO `考核主表` VALUES ('1', '5', '100', null);
INSERT INTO `考核主表` VALUES ('2', '3', '50', null);
Navicat MySQL Data Transfer
Source Server         : 127.0.0.1
Source Server Version : 80015
Source Host           : localhost:3306
Source Database       : fr
Target Server Type    : MYSQL
Target Server Version : 80015
File Encoding         : 65001
Date: 2022-08-20 18:44:04
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for 考核明细
-- ----------------------------
DROP TABLE IF EXISTS `考核明细`;
CREATE TABLE `考核明细` (
  `考核明细ID` int(11) NOT NULL AUTO_INCREMENT,
  `考核内容` varchar(255) DEFAULT NULL,
  `考核负责人` varchar(255) DEFAULT NULL,
  `考核明细得分` int(11) DEFAULT NULL,
  `考核主表ID` int(11) DEFAULT NULL,
  PRIMARY KEY (`考核明细ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of 考核明细
-- ----------------------------
INSERT INTO `考核明细` VALUES ('1', 'A', 'sjk', null, '1');
INSERT INTO `考核明细` VALUES ('2', 'B', 'lt', null, '1');
INSERT INTO `考核明细` VALUES ('3', 'C', 'syc', null, '2');
Navicat MySQL Data Transfer
Source Server         : 127.0.0.1
Source Server Version : 80015
Source Host           : localhost:3306
Source Database       : fr
Target Server Type    : MYSQL
Target Server Version : 80015
File Encoding         : 65001
Date: 2022-08-20 18:44:15
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for 考核项目填报明细
-- ----------------------------
DROP TABLE IF EXISTS `考核项目填报明细`;
CREATE TABLE `考核项目填报明细` (
  `填报明细ID` int(11) NOT NULL AUTO_INCREMENT,
  `填报时间` datetime DEFAULT NULL,
  `奖罚得分` int(11) DEFAULT NULL,
  `考核明细ID` int(11) DEFAULT NULL,
  PRIMARY KEY (`填报明细ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of 考核项目填报明细
-- ----------------------------
INSERT INTO `考核项目填报明细` VALUES ('1', '2022-08-19 16:30:17', '-10', '1');
INSERT INTO `考核项目填报明细` VALUES ('2', '2022-08-20 16:30:41', '-20', '1');
INSERT INTO `考核项目填报明细` VALUES ('3', '2022-08-16 16:42:22', '-50', '2');
INSERT INTO `考核项目填报明细` VALUES ('4', '2022-08-02 16:42:39', '-70', '2');

创建中间的视图

获取考核填报明细分组的最新一条的表

。针对这个单表操作。约束的条件，是分组中，根据这个分组id及时间为一条。在同一个分组下，时间不能一样。否则在join 的时候，会出现多条记录

SELECT
	`a`.`填报明细ID` AS `填报明细ID`,
	`a`.`填报时间` AS `填报时间`,
	`a`.`奖罚得分` AS `奖罚得分`,
	`a`.`考核明细ID` AS `考核明细ID`
		`考核项目填报明细` `a`
		JOIN (
			SELECT
				`考核项目填报明细`.`考核明细ID` AS `考核明细ID`,
					`考核项目填报明细`.`填报时间`
				) AS `填报时间`
				`考核项目填报明细`
			GROUP BY
				`考核项目填报明细`.`考核明细ID`
		) `s` ON (
					`a`.`考核明细ID` = `s`.`考核明细ID`
				AND (
					`a`.`填报时间` = `s`.`填报时间`
	)

创建汇总表View

SELECT
	`考核主表`.`考核ID` AS `考核ID`,
	`考核主表`.`权重` AS `权重`,
	`考核主表`.`计算参考分数` AS `计算参考分数`,
	`考核主表`.`汇总得分` AS `汇总得分`,
	`考核明细`.`考核明细ID` AS `考核明细ID`,
	`考核明细`.`考核内容` AS `考核内容`,
	`考核明细`.`考核负责人` AS `考核负责人`,
	`考核明细`.`考核明细得分` AS `考核明细得分`,
	`考核项目填报明细最新条目view`.`填报时间` AS `填报时间`,
	`考核项目填报明细最新条目view`.`填报明细ID` AS `填报明细ID`,
	`考核项目填报明细最新条目view`.`奖罚得分` AS `奖罚得分`
			`考核主表`
			LEFT JOIN `考核明细` ON (
					`考核主表`.`考核ID` = `考核明细`.`考核主表ID`
		LEFT JOIN `考核项目填报明细最新条目view` ON (
				`考核项目填报明细最新条目view`.`考核明细ID` = `考核明细`.`考核明细ID`
	)

计算汇总得分的SQL

SELECT
	考核主表.*, s.`奖罚汇总`
	`考核主表`
LEFT JOIN (
	SELECT
		考核ID,
		ISNULL(sum(`奖罚得分`)),
		sum(`奖罚得分`)