为什么不能为具有最大日期的每个代码选择记录，并且具有日期=max(date)？

Question

使用显示创建表结构；

CREATE TABLE `quote` (
`id` int(8) unsigned NOT NULL AUTO_INCREMENT,
`code` text COLLATE utf8mb4_unicode_ci,
`date` date DEFAULT NULL,
`open` double DEFAULT NULL,
`high` double DEFAULT NULL,
`low` double DEFAULT NULL,
`close` double DEFAULT NULL,
`volume` bigint(15) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17449887 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 

查找具有内部联接的最大日期的每个代码的记录，例如：

SELECT q1.*
FROM quote q1
INNER JOIN
(
SELECT code, MAX(date) AS max_date
FROM quote
GROUP BY code
) q2
ON q2.code = q1.code AND
q2.max_date = q1.date;

我想知道为什么子查询不能得到如上所述的期望结果：

select * from quote group by code having date=max(date);

请详细解释原因。

Answer 1

此

select * from quote group by code having date=max(date);

具有CCD_ 1，这在CCD_。但date没有。问题是应该比较哪个行的date？简单地说，它可能是"无效的"SQL。

另见"；only_full_group_by"；。(MySQL的较新版本会将您的查询标记为无效。该标记是关闭它以获得旧的、错误的评估的一种方法。(

这会导致子查询，例如您所拥有的子查询。还有其他一些。以下是我的最佳分组最大化方法目录：http://mysql.rjweb.org/doc.php/groupwise_max

还有许多讨论；请参阅我添加的标签[groupwise-maximum]。

其他问题：code是股票代码吗？如果是，则不需要是TEXT。通过更改为VARCHAR(15)，您可以获得很多性能：

去掉id，改为max(date)0。这将显著地扩展该子查询，并可能改进其他一些查询。

Answer 2

我想知道为什么子查询不能得到如上所述的期望结果：

select * from quote group by code having date=max(date);

开始：

select * from quote group by code

从SQL Standard的角度来看，此查询本身不是有效的查询。

如果所有其他列在功能上都依赖于code，则可能是这样，而基于表定义的情况并非如此(代码不是唯一的，也不是主键(。相关阅读：mySQL和postgreSQL中的Group by子句，为什么postgreSQL出现错误？

查询的行为有点像ANY_VALUE:

select code, ANY_VALUE(id), ANY_VALUE(`date`), ANY_VALUE(`open`)...
from quote
group by code

关于第二部分：

having date=max(date);
--
having any_value(date) = max(date) -- sidenote: it will work for single row per `code`

这里，HAVING中的条件在聚合之后适用，这意味着每个代码的MAX(日期(与"；未指明的"；日期

---

举例说明(此代码仅在only_full_group_by关闭时有效(：

CREATE TABLE `quote` (
`id` int(8) unsigned NOT NULL AUTO_INCREMENT,
`code` text COLLATE utf8mb4_unicode_ci,
`date` date DEFAULT NULL,
`open` double DEFAULT NULL,
PRIMARY KEY (`id`)
) ;
INSERT INTO quote(`code`, `date`, `open`)
VALUES ('a', '2020-01-01',10),
('a', '2021-01-01',20),
('a', '2022-01-01',30);

查询：

SELECT * FROM quote;
+-----+-------+-------------+------+
| id  | code  |    date     | open |
+-----+-------+-------------+------+
|  1  | a     | 2020-01-01  |   10 |
|  2  | a     | 2021-01-01  |   20 |
|  3  | a     | 2022-01-01  |   30 |
+-----+-------+-------------+------+
select * from quote group by code;
-- this part is unspecified, id/date/open are arbitrary
+-----+-------+-------------+------+
| id  | code  |    date     | open |
+-----+-------+-------------+------+
|  1  | a     | 2020-01-01  |    1 |
+-----+-------+-------------+------+
select *, MAX(date) from quote group by code;
-- MAX(date) is stable, date is arbitrary, comparison does not make sense at this point
+-----+-------+-------------+-------+------------+
| id  | code  |    date     | open  | MAX(date)  |
+-----+-------+-------------+-------+------------+
|  1  | a     | 2020-01-01  |   10  | 2022-01-01 |  
+-----+-------+-------------+-------+------------+
select * from quote group by code having date=max(date);
-- empty
+-----+-------+-------+------+
| id  | code  | date  | open |
+-----+-------+-------+------+

db<gt；小提琴演示

---

这么说，为了获得所有列的排名函数，MySQL 8.0+可以使用：

本节介绍非聚合窗口函数，这些函数针对查询中的每一行，使用与该行相关的行执行计算

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY `code` ORDER BY `date` DESC) AS rn
FROM `quote`) s   --RANK() if `date` is not unique per code
WHERE rn = 1;

db<gt；小提琴演示2

Answer 3

@scracy我相信你的问题的答案与子查询和其他查询的结构之间的区别有关。子查询的工作方式是MySQL在运行外部查询之前从查询创建一个派生表，然后在MySQL执行外部查询时使用派生表(以下是有关派生表的文档供您参考(。

您的子查询之所以有效，是因为您只选择了1列(code(，然后使用MAX(date)作为第二列来获得聚合值，最后在子查询的最后一行按code进行分组。

在第二个查询中，在尝试在GROUP BY code0子句中使用MAX(date)之前，先使用SELECT *，然后仅按code进行分组。此查询不起作用，因为您使用SELECT *选择表中的每一列，但在GROUP BY子句中仅使用code进行分组。从MySQL v5.7及更高版本开始，有一种名为only_full_group_by的东西不允许您使用GROUP BY运行查询，除非您在GROUP BY中指定SELECT语句中的每一列，IE：要使第二个查询工作，您必须在GROUP BY子句中列出表中的每一列，因为您的select语句使用的是SELECT *(这是关于only_full_group_by的文档(。

最后，为了获得您要查找的结果集，您必须按照正确的列进行分组，就像您在子查询中所做的那样。如果您在查询中使用code以外的任何内容，试图获得每个code的最大日期，则结果集将不相同，因为您必须按额外的列进行分组，这将丢弃您的结果集。