需求
需要从历史记录中，统计每个项目最新的电费信息
建表
DROP TABLE IF EXISTS `demo_ec_balance_history`;
CREATE TABLE `demo_ec_balance_history` (
`id` bigint(20) NOT NULL COMMENT '设备主键id',
`ec_balance` decimal(20, 2) NULL DEFAULT NULL COMMENT '电费余额',
`prj_id` bigint(20) NULL DEFAULT NULL COMMENT '项目id（业务分组id）',
`create_by` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人id',
`create_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人姓名',
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`update_by` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '修改人id',
`update_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '修改人姓名',
`update_time` datetime(0) NULL DEFAULT NULL COMMENT '修改时间',
`is_delete` int(11) NULL DEFAULT 0 COMMENT '删除状态',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '电费历史表（ec=electric_charge）' ROW_FORMAT = Dynamic;
导入数据
INSERT INTO `demo_ec_balance_history` VALUES (1505011798281129985, 99.00, 1504640718977150978, 'demo', 'demo', '2022-03-19 10:42:29', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1505027351108489218, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-19 11:44:17', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1505069915446546434, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-19 14:33:25', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1505710632455491585, 33.00, 1504640718977150978, 'demo', 'demo', '2022-03-21 08:59:24', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1505738100059000833, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-21 10:48:33', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1506097663752916994, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-22 10:37:20', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1506105329560702977, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-22 11:07:47', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1506815487886610433, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-24 10:09:42', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1506815520803508225, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-24 10:09:50', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1506815546682363906, 11.00, 1504640718977150978, 'demo', 'demo', '2022-03-24 10:09:56', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1506815573718847490, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-24 10:10:03', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1506815602399498241, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-24 10:10:10', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1506819227775520769, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-24 10:24:34', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1506831590633361409, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-24 11:13:41', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1506914025819721730, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-24 16:41:16', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1506914047059677185, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-24 16:41:21', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1507187939930132482, 158.00, 1504640718977150978, 'demo', 'demo', '2022-03-25 10:49:42', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1507195418290278402, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-25 11:19:25', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1507195440067104770, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-25 11:19:30', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1507195460887629825, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-25 11:19:35', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1507195489010438146, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-25 11:19:42', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1507195513756831746, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-25 11:19:47', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1507196215799435265, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-25 11:22:35', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1507267279770472449, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-25 16:04:58', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1507268908850728961, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-25 16:11:26', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1507269675644997634, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-25 16:14:29', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1507273391584432129, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-25 16:29:15', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1508383349755465730, 22.50, 1504640718977150978, 'demo', 'demo', '2022-03-28 17:59:50', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1509002702271066113, 200.00, 1504640718977150978, 'demo', 'demo', '2022-03-30 11:00:55', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1509437507152424961, 44.00, 1504640718977150978, 'demo', 'demo', '2022-03-31 15:48:40', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1509437536613216258, 1.00, 1504640718977150978, 'demo', 'demo', '2022-03-31 15:48:47', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1509437562575958017, 33.30, 1504640718977150978, 'demo', 'demo', '2022-03-31 15:48:54', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1509437587943108609, 22.00, 1504640718977150978, 'demo', 'demo', '2022-03-31 15:49:00', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1509437614065233921, 22.00, 1504640718977150978, 'demo', 'demo', '2022-03-31 15:49:06', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1509437640594206721, 100.00, 1504640718977150978, 'demo', 'demo', '2022-03-31 15:49:12', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1509437652938043393, 2.00, 1504640718977150978, 'demo', 'demo', '2022-03-31 15:49:15', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1509437679592845313, 1.00, 1504640718977150978, 'demo', 'demo', '2022-03-31 15:49:21', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1509443959334154241, 0.00, 1504640718977150978, 'demo', 'demo', '2022-03-31 16:14:19', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1509703910942093314, 0.00, 1504640718977150978, 'demo', 'demo', '2022-04-01 09:27:16', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1509703932161077249, 0.00, 1504640718977150978, 'demo', 'demo', '2022-04-01 09:27:21', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1509703953380061185, 0.00, 1504640718977150978, 'demo', 'demo', '2022-04-01 09:27:26', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1509703972476727297, 0.00, 1504640718977150978, 'demo', 'demo', '2022-04-01 09:27:31', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1509703990180884482, 0.00, 1504640718977150978, 'demo', 'demo', '2022-04-01 09:27:35', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1509704008501604354, 0.00, 1504640718977150978, 'demo', 'demo', '2022-04-01 09:27:39', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1509704028491657217, 0.00, 1504640718977150978, 'demo', 'demo', '2022-04-01 09:27:44', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1509704047454105601, 0.00, 1504640718977150978, 'demo', 'demo', '2022-04-01 09:27:49', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1509704065544138753, 0.00, 1504640718977150978, 'demo', 'demo', '2022-04-01 09:27:53', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1509704281248804865, 0.00, 1504640718977150978, 'demo', 'demo', '2022-04-01 09:28:44', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1509707183287742465, 0.00, 1504640718977150978, 'demo', 'demo', '2022-04-01 09:40:16', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1511971609062195202, 1000.00, 1504640718977150978, 'demo', 'demo', '2022-04-07 15:38:17', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1513416993319993345, 22.00, 1504640718977150978, 'demo', 'demo', '2022-04-11 15:21:44', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1513786160078438402, 66.00, 1513451777047969794, 'demo', 'demo', '2022-04-12 15:48:40', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1516717258418688002, 22.00, 1504640718977150978, 'demo', 'demo', '2022-04-20 17:55:48', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1516717258418688003, 22.00, 1504640718977150978, 'demo', 'demo', '2022-04-20 17:55:48', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1518141321695465474, 33.00, 1506082764695916546, 'demo', 'demo', '2022-04-24 16:14:31', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1518141739276177409, 44.00, 1506082764695916546, 'demo', 'demo', '2022-04-24 16:16:11', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1518142279636750337, 66.00, 1506082764695916546, 'demo', 'demo', '2022-04-24 16:18:20', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1528656904774602754, 60.00, 1528627070056910850, 'demo', 'demo', '2022-05-23 16:39:42', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1528656923753828353, 20.00, 1528627070056910850, 'demo', 'demo', '2022-05-23 16:39:46', NULL, NULL, NULL, 0);
INSERT INTO `demo_ec_balance_history` VALUES (1528674578481139713, 30.00, 1528627070056910850, 'demo', 'demo', '2022-05-23 17:49:56', NULL, NULL, NULL, 0);
示例
查询一个项目按时间最新一条记录（缺点：如果有100个项目，则需要循环查询，性能非常差）
SELECT
a.*
FROM
demo_ec_balance_history a
WHERE
a.prj_id = 1504640718977150978
ORDER BY
a.create_time DESC
LIMIT 1;
结果
查询每个项目按时间最新的一条记录（优点：现在一个SQL就可以查询出所有项目；缺点：如果存在同样的项目和时间，则结果就不正确）
SELECT
a.*
FROM
demo_ec_balance_history a
INNER JOIN (
SELECT
a.prj_id,
MAX(a.create_time) create_time
FROM
demo_ec_balance_history a
GROUP BY a.prj_id
) b ON a.prj_id = b.prj_id AND a.create_time = b.create_time;
结果：下面的结果有问题，同样的项目和时间存在2条
查询每个项目按时间最新的一条记录（优点：存在同样的项目和时间，则使用MAX(id)选择最大id那个即可）
SELECT
a.*
FROM
demo_ec_balance_history a
INNER JOIN (
SELECT
a.prj_id,
MAX(a.create_time) create_time,
MAX(a.id) id
FROM
demo_ec_balance_history a
GROUP BY a.prj_id
) b ON a.prj_id = b.prj_id AND a.create_time = b.create_time AND a.id = b.id;
结果：如下图，按分组查询到项目最新的4条记录
查询每个项目按电费余额最大值最新的一条记录（注意MAX( a.ec_balance ),MAX(a.id)不能直接一起使用）
SELECT
a.*
FROM
demo_ec_balance_history a
INNER JOIN (
SELECT
a.prj_id,
MAX( a.id ) id
FROM
demo_ec_balance_history a
INNER JOIN ( SELECT a.prj_id, MAX( a.ec_balance ) ec_balance FROM demo_ec_balance_history a GROUP BY a.prj_id ) b ON a.prj_id = b.prj_id AND a.ec_balance = b.ec_balance
GROUP BY
a.prj_id
) b ON a.id = b.id
查询每个项目按电费余额最小值最新的一条记录（参考电费余额最大值）
SELECT
a.*
FROM
demo_ec_balance_history a
INNER JOIN (
SELECT
a.prj_Id,
MIN( a.id ) id
FROM
demo_ec_balance_history a
INNER JOIN ( SELECT a.prj_id, MIN( a.ec_balance ) ec_balance FROM demo_ec_balance_history a GROUP BY a.prj_id ) b ON a.prj_id = b.prj_id AND a.ec_balance = b.ec_balance
GROUP BY
a.prj_id
) b ON a.id = b.id
