mysql - 嵌套 VIEW 忽略 ORDER BY

Question

类似这个问题: MySQL: View with Subquery in the FROM Clause Limitation

我有以下显示表:

DROP TABLE IF EXISTS `shows`;
CREATE TABLE `shows` (
  `show_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `show_type` int(11) unsigned DEFAULT NULL,
  `show_year` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`show_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `shows` VALUES
(NULL, 1, 2014), -- 1
(NULL, 1, 2015), -- 2
(NULL, 2, 2015), -- 3
(NULL, 2, 2014); -- 4

我想创建一个 VIEW，它将为每个 show_type 返回 show_id highest show_year。这是一个有效的嵌套查询 - 返回 2 和 3:

SELECT s.show_id, s.show_year
FROM ( -- subquery for applying ORDER BY before GROUP BY
    SELECT *
    FROM shows
    ORDER BY show_year DESC
) s
GROUP BY show_type;
/*
+---------+-----------+
| show_id | show_year |
+---------+-----------+
|       2 |      2015 |
|       3 |      2015 |
+---------+-----------+
*/

仅供引用，我还尝试了以下查询，起初对我来说似乎很自然，但在我的情况下它最终不好，如下所示:

SELECT s.show_id, MAX(s.show_year)
FROM shows s
GROUP BY show_type;
/*
+---------+------------------+
| show_id | MAX(s.show_year) |
+---------+------------------+
|       1 |             2015 | <== show_id=1 does NOT have show_year=2015
|       3 |             2015 |
+---------+------------------+
*/

现在创建一个 View - 基于上面的嵌套查询(第一个 SELECT)，问题是 a view will not accept a subquery .

所以我改用两个 View 。一个在另一个里面。

第一个只是按 show_year DESC 对表格进行排序:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `shows_desc` AS
SELECT `s1`.`show_id` AS `show_id`,
    `s1`.`show_type` AS `show_type`,
    `s1`.`show_year` AS `show_year`
FROM `shows` `s1`
ORDER BY `s1`.`show_year` DESC;

第二个应该对第一个进行 GROUP BY:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `shows_grouped` AS
SELECT `s2`.`show_id` AS `show_id`,
    `s2`.`show_year` AS `show_year`
FROM `shows_desc` `s2`
GROUP BY `s2`.`show_type`;

然而，令我惊讶的是它返回了不正确的行。就好像它 GROUPed ignoring subview 的 ORDER:

+---------+-----------+
| show_id | show_year |
+---------+-----------+
|       3 |      2015 |
|       1 |      2014 | <== why?
+---------+-----------+

线索？

P.S.: SQL fiddle to poke: http://sqlfiddle.com/#!2/e506d4/5

Answer 1

让我专注于此:

I want to create a VIEW that will return show_id for the highest show_year for each show_type. Here's a nested query that works - returns 2 and 3:

这是一种方法，假设 show_id 递增，因此最大的 show_id 是最近一年的:

select show_type, max(show_year) as show_year, max(show_id)
from shows
group by show_type;

如果没有，试试这个:

select show_type, max(show_year) as show_year,
       substring_index(group_concat(show_id order by show_year desc), ',', 1) as show_id
from shows
group by show_type;

您的其他查询不起作用的原因是您对 MySQL 的工作原理有“一厢情愿”的理解。您正在使用 MySQL 的扩展，明确说它不能在 documentation 中工作。 .也就是说，select 中的列不在聚合函数中，也不在 group by 子句中(并且在功能上不相关，但这是一个非常高级的概念与本次讨论无关):

In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate, which is probably not what you want.

编辑:

以下内容也适用于 View :

select s.*
from shows s
where s.show_year = (select max(s2.show_year) from shows s2 where s2.show_type = s.show_type);

编辑二:

如果每个 show_type 只需要一行并且 show_id 是唯一的，那么这应该可行:

select s.*
from shows
where not exists (select 1
                  from shows s2
                  where s2.show_type = s.show_type and
                        (s2.show_year > s.show_year or
                         s2.show_year = s.show_year and s2.show_id > s.show_id
                        )
                 );