MySQL 子选择然后组不工作

Question

我有这样的表结构:

CREATE TABLE `table_a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col_a_one` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `table_b` (
  `id` int(11) NOT NULL,
  `table_a_id` int(11) DEFAULT NULL,
  `col_b_one` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_a_idx` (`table_a_id`),
  CONSTRAINT `fk_a` FOREIGN KEY (`table_a_id`) REFERENCES `table_a` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

使用这个值:

INSERT INTO table_a VALUES (1, 'First Row');
INSERT INTO table_a VALUES (2, 'Second Row');

INSERT INTO table_b VALUES (1,1,'Jan 2017');
INSERT INTO table_b VALUES (2,1,'Feb 2017');
INSERT INTO table_b VALUES (3,2,'Jun 2018');
INSERT INTO table_b VALUES (4,2,'Jul 2018');
INSERT INTO table_b VALUES (5,1,'Mar 2017');

表:表_a

id, col_a_one
1, First Row
2, Second Row

表:table_b

id, table_a_id, col_b_one
1, 1, Jan 2017
2, 1, Feb 2017
3, 2, Jun 2018
4, 2, Jul 2018
5, 1, Mar 2017

在我的计算机中，如果我执行此查询:

SELECT
    c.*
FROM
(
    SELECT 
        t1.`id` AS 'table_a_id',
        t2.`id` AS 'table_b_id',
        t1.`col_a_one`,
        t2.`col_b_one`,
        DATE_FORMAT(STR_TO_DATE(t2.`col_b_one`, '%b %Y'), "%Y-%m-01") AS 'test_column'
    FROM `table_a` t1
         INNER JOIN `table_b` t2
            ON t1.`id` = t2.`table_a_id`
    ORDER BY
        t1.`id` ASC,
        DATE_FORMAT(STR_TO_DATE(t2.`col_b_one`, '%b %Y'), "%Y-%m-01") DESC
) AS c
GROUP BY
    c.`table_a_id`

它会返回错误的结果:

table_a_id, table_b_id, col_a_one, col_b_one, test_column
1, 1, First Row, Jan 2017, 2017-01-01
2, 3, Second Row, Jun 2018, 2018-06-01

但是如果我在sqlfiddle中执行:http://sqlfiddle.com/#!9/5a1f0/1结果正如我所料:

table_a_id, table_b_id, col_a_one, col_b_one, test_column
1, 5, First Row, Mar 2017, 2017-03-01
2, 4, Second Row, Jul 2018, 2018-07-01

我使用的是MySQL 5.7，这是我使用的sql_mode:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

我的 MySQL 服务器出了什么问题...？

Answer 1

编辑2

根据进一步的评论，这可能就是您想要的。它从 table_b 中选择与该表中每 table_a 行的最新日期相对应的所有值:

SELECT a.id AS table_a_id,
    a.col_a_one,
    DATE_FORMAT(STR_TO_DATE(b1.col_b_one, '%b %Y'), "%b %Y") AS 'latest_date',
    COUNT(b.id) AS 'total_item_b',
    b1.col_b_two
FROM table_a a
JOIN table_b b ON b.table_a_id = a.id
JOIN table_b b1 ON b1.table_a_id = a.id AND
    STR_TO_DATE(b1.`col_b_one`, '%b %Y') = (SELECT MAX(STR_TO_DATE(`col_b_one`, '%b %Y'))
                                            FROM table_b
                                            WHERE table_b.table_a_id = a.id)
GROUP BY a.id

输出:

table_a_id  col_a_one   latest_date     total_item_b  col_b_two
1           First Row   Mar 2017        3             Y
2           Second Row  Jul 2018        2             M

已更新SQLFiddle

编辑

根据下面的评论，我认为这个查询是您真正想要的:

SELECT a.id AS table_a_id,
    a.col_a_one,
    DATE_FORMAT(MAX(STR_TO_DATE(b.col_b_one, '%b %Y')), "%b %Y") AS 'latest_date',
    COUNT(b.id) AS 'total_item_b'
FROM table_a a
JOIN table_b b ON b.table_a_id = a.id
GROUP BY a.id

这将返回 table_a 中每一行的 table_b 中的项目数，以及最新(最新)日期:

table_a_id  col_a_one   latest_date     total_item_b
1           First Row   Mar 2017        3
2           Second Row  Jul 2018        2

已更新SQLFiddle

正如@RaymondNijland 指出的，您没有正确使用GROUP BY。分组时，非聚合列和非分组列将返回随机值，这就是为什么与 table_a_id 不直接相关的所有内容都会得到不同的结果。看来您想要实现的目标是返回 table_a_id 的值，该值对于每个 table_a_id 具有最大值 col_b_one ，您可以执行此查询:

SELECT 
    a.`id` AS 'table_a_id',
    b.`id` AS 'table_b_id',
    a.`col_a_one`,
    b.`col_b_one`
FROM `table_a` a
JOIN `table_b` b ON a.`id` = b.`table_a_id` AND
  STR_TO_DATE(b.`col_b_one`, '%b %Y') = (SELECT MAX(STR_TO_DATE(`col_b_one`, '%b %Y'))
                                         FROM table_b
                                         WHERE table_b.table_a_id = a.id)
ORDER BY a.id ASC

输出:

table_a_id  table_b_id  col_a_one   col_b_one
1           5           First Row   Mar 2017
2           4           Second Row  Jul 2018

我已经更新了您的SQLFiddle来演示。