mysql - 嵌套的VIEW忽略ORDER BY




nested subquery (2)

與此類似的問題: MySQL:在FROM子句限制中使用子查詢進行查看

我有以下shows表格:

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_yearshow_type 。 這是一個嵌套的查詢工作 - 返回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 |
+---------+------------------+
*/

現在基於上面的嵌套查詢(第一個SELECT)創建一個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 忽略子視圖的ORDER:

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

線索?

PS:SQL小提琴捅: http ://sqlfiddle.com/#!2/e506d4/5


我想創建一個VIEW,將返回每個show_type的最高show_year的show_id。

select s.show_id
from shows s
where s.show_year=
    (select max(st.show_year)
    from shows st
    where st.show_type=s.show_type)

(正如Gordon Linoff的回答 )

一般來說,可以有多個show_id,其中show_year是給定show_type的最大值。 (他也在評論中指出)

如果你只想返回其中的一個,那就說出你的問題中的哪一個。 最大的一個:

select s.show_id
from shows s
where s.show_year=
    (select max(st.show_year)
    from shows st
    where st.show_type=s.show_type)
and s.show_id=
    (select max(st.show_id)
    from shows st
    where st.show_type=s.show_type
    and st.show_year=s.show_year);

使用每個類型最大年份的ID的嵌套視圖:

CREATE VIEW `shows_1` AS
SELECT `show_type` AS `show_type`,
    MAX(`show_year`) AS `show_year`
FROM `shows`
GROUP BY `show_type`;

CREATE VIEW `shows_ids` AS
SELECT `s`.`show_id`
FROM `shows` `s`
JOIN `shows_1` `s1`
ON `s`.`show_type`=`s1`.`show_type`
AND `s`.`show_year`=`s1`.`show_year`;

或者,如果您希望每個show_type的最大show_id和最大show_year:

CREATE VIEW `shows_id` AS
SELECT MAX(`s`.`show_id`) AS `show_id`
FROM `shows` `s`
JOIN `shows_1` `s1`
ON `s`.`show_type`=`s1`.`show_type`
AND `s`.`show_year`=`s1`.`show_year`
GROUP BY `s`.`show_type`,`s`.`show_year`;

那麼ORDER BY如何在VIEW中的GROUP BY之前應用呢?

如果你想命令一個SELECT的結果,那麼你必須在其GROUP BY之後的ORDER BY中執行。 因此,對於GROUP BY之前的ORDER BY,您必須使用帶有GROUP BY的外部SELECT。 如果他的外部GROUP BY SELECT在視圖中,那麼INNER BY ORDER BY SELECT可能必須位於第一個命名的另一個視圖中。

但是在查詢中命名的表或視圖的順序不是通過計算結果來保存的。 那麼為什麼要在GROUP BY之前進行ORDER BY呢? 為什麼你認為你的問題的嵌套視圖應該返回每show_type最大show_year?


我把重點放在這個:

我想創建一個VIEW,將返回每個show_type的最高show_year的show_id。 這是一個嵌套的查詢工作 - 返回2和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的擴展, 明確表示不在文檔中工作。 也就是說, select中的列不在聚合函數中,也不在group by子句中(並且在功能上並不依賴,但是這是一個非常先進的概念,與本討論無關):

在這種情況下,服務器可以自由選擇每個組中的任何值,所以除非它們相同,否則所選的值是不確定的,這可能不是您想要的。

編輯:

以下內容也適用於一個視圖:

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

編輯II:

如果每個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
                        )
                 );




greatest-n-per-group