MySql5.8 OVER函数，查询出每只基金的最新净值

MySql5.8 用OVER函数，查询出每只基金的最新净值

需求

1:如下两张表:

请用一条Sql语句,查询出每只基金的最新净值 , MySQL版本>=5.7

基金表: funds

id	主键id
name	基金名称

基金净值表: funds_net_worth

注: 基金净值表里每只基金会有多个净值,并且同一时间净值日期只会存在一个净值

n_id	主键id
n_funds_id	基金id
n_net_worth	基金净值
n_net_worth_time	净值日期

表数据如下:

  funds:

id	name
1	SMA-1
2	SMA-2

funds_net_worth:

n_id	n_funds_id	n_net_worth	n_net_worth_time
1	1	2.3	2022/8/16 13:51
2	1	2.4	2022/8/1 13:51
3	1	2	2022/8/3 13:51
4	1	5	2022/8/12 13:51
5	1	4.5	2022/8/30 13:51
6	2	4.3	2022/8/16 13:51
7	2	4.4	2022/8/1 13:51
8	2	4	2022/8/3 13:51
9	2	3	2022/8/12 13:51
10	2	2.5	2022/8/30 13:51

查询结果

name	n_funds_id	n_net_worth	n_net_worth_time
SMA-1	1	1.27	2022-08-30 13:51:29
SMA-2	2	1.27	2022-08-30 13:51:29

建表语句

CREATE TABLE `funds` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(255) DEFAULT NULL COMMENT '基金名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `test`.`funds`(`id`, `name`) VALUES (1, 'SMA-1');
INSERT INTO `test`.`funds`(`id`, `name`) VALUES (2, 'SMA-2');

CREATE TABLE `funds_net_worth` (
  `n_id` int NOT NULL AUTO_INCREMENT,
  `n_funds_id` int DEFAULT NULL COMMENT '基金Id',
  `n_net_worth` decimal(10,5) DEFAULT NULL COMMENT '基金净值',
  `n_net_worth_time` datetime DEFAULT NULL COMMENT '净值日期',
  PRIMARY KEY (`n_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `test`.`funds_net_worth`(`n_id`, `n_funds_id`, `n_net_worth`, `n_net_worth_time`) VALUES (1, 1, 2.30000, '2022-08-16 13:51:29');
INSERT INTO `test`.`funds_net_worth`(`n_id`, `n_funds_id`, `n_net_worth`, `n_net_worth_time`) VALUES (2, 1, 2.40000, '2022-08-01 13:51:29');
INSERT INTO `test`.`funds_net_worth`(`n_id`, `n_funds_id`, `n_net_worth`, `n_net_worth_time`) VALUES (3, 1, 2.00000, '2022-08-03 13:51:29');
INSERT INTO `test`.`funds_net_worth`(`n_id`, `n_funds_id`, `n_net_worth`, `n_net_worth_time`) VALUES (4, 1, 5.00000, '2022-08-12 13:51:29');
INSERT INTO `test`.`funds_net_worth`(`n_id`, `n_funds_id`, `n_net_worth`, `n_net_worth_time`) VALUES (5, 1, 4.50000, '2022-08-30 13:51:29');
INSERT INTO `test`.`funds_net_worth`(`n_id`, `n_funds_id`, `n_net_worth`, `n_net_worth_time`) VALUES (6, 2, 4.30000, '2022-08-16 13:51:29');
INSERT INTO `test`.`funds_net_worth`(`n_id`, `n_funds_id`, `n_net_worth`, `n_net_worth_time`) VALUES (7, 2, 4.40000, '2022-08-01 13:51:29');
INSERT INTO `test`.`funds_net_worth`(`n_id`, `n_funds_id`, `n_net_worth`, `n_net_worth_time`) VALUES (8, 2, 4.00000, '2022-08-03 13:51:29');
INSERT INTO `test`.`funds_net_worth`(`n_id`, `n_funds_id`, `n_net_worth`, `n_net_worth_time`) VALUES (9, 2, 3.00000, '2022-08-12 13:51:29');
INSERT INTO `test`.`funds_net_worth`(`n_id`, `n_funds_id`, `n_net_worth`, `n_net_worth_time`) VALUES (10, 2, 2.50000, '2022-08-30 13:51:29');

答案：

1：MySQL 5.8 的OVER()函数

SELECT
	* 
FROM
	funds
	LEFT JOIN (
SELECT
	funds_net_worth.n_id,
	funds_net_worth.n_funds_id,
	funds_net_worth.n_net_worth,
	funds_net_worth.n_net_worth_time,
	row_number ( ) over ( PARTITION BY n_funds_id ORDER BY n_net_worth_time DESC ) AS order_row 
FROM
	funds_net_worth 
	) t_n ON ( id = n_funds_id ) 
WHERE
	order_row = 1

2：MySQL 5.7写法

SELECT
	f.NAME,
	fnw.n_funds_id,
	fnw.n_net_worth,
	fnw.n_net_worth_time 
FROM
	funds f
	INNER JOIN funds_net_worth fnw ON f.id = fnw.n_funds_id
	INNER JOIN ( SELECT n_funds_id, max( n_net_worth_time ) AS timeMax FROM funds_net_worth GROUP BY n_funds_id ) fnw2 ON fnw.n_funds_id = fnw2.n_funds_id 
	AND fnw.n_net_worth_time = fnw2.timeMax 
WHERE
	1 = 1 
ORDER BY
	fnw.n_funds_id