mysql - MySQL：新数据库版本的子查询中的旧 ORDER BY

Question

大家好，希望你一切都好。

我没有提出很多问题，所以我会尽力做到最好......

我有以下示例表：

CREATE TABLE `e_rsts_test` (
  `id` int(11),
  `id_book` int(11),
  `lang` varchar(2),
  `title` varchar(100)
);

INSERT INTO `e_rsts_test` (`id`, `id_book`, `lang`, `title`) VALUES
(33, 51, 'es', 'Tocar los libros'),
(409, 51, 'en', NULL),
(34, 52, 'de', 'Kaput'),
(35, 53, 'fr', 'Chróniques de la dernière Révolution'),
(36, 54, 'es', 'Veneno para la corona'),
(412, 54, 'en', NULL),
(313, 51, 'it', 'Toccare i libri'),
(314, 53, 'en', 'Cronicles of revolution');

结果如下表：

id |id_book|lang|title
33 |51     |es  |Tocar los libros
409|51     |en  |NULL
34 |52     |de  |Kaput
35 |53     |fr  |Chróniques de la dernière Révolution
36 |54     |es  |Veneno para la corona
412|54     |en  |NULL
313|51     |it  |Toccare i libri
314|53     |en  |Cronicles of revolution

我需要从中获取一个新表，仅选择不为 NULL 的标题，如果有多个标题，请按照语言优先顺序选择记录，因此：如果存在“es”语言的标题，则, else if 存在于 'en', 那一个，否则是任何其他语言的标题。在前面的示例中，我需要作为结果获取的表是：

id |id_book|lang|title
33 |51     |es  |Tocar los libros
34 |52     |de  |Kaput
314|53     |en  |Cronicles of revolution
36 |54     |es  |Veneno para la corona

我曾经在我的旧 MySQL 数据库中解决这个问题，按以下方式订购子查询：

SELECT id_book_title, title
FROM (SELECT id_book as id_book_title, title
      FROM e_rsts_test
      ORDER BY ISNULL(title) DESC, (lang = 'es') DESC, (lang = 'en') DESC, id) as c_aux1
GROUP BY id_book_title

但是在新的 MySQL DB 版本中（开发环境为 10.4.13-MariaDB，运行环境为 8.0.19），ORDER BY 不起作用，所以我在结果表中随机获取了一个 NULL 标题。

注意：当然，数据库中存在的标题语言可以上升到 100 多种，排序标准取决于站点访问者浏览器的语言配置。注意 2：每个 id_book 至少有一个非 NULL 标题。

在最新的 MySQL DB 版本中是否有任何解决方案或周转来解决此问题？我搜索但我找不到任何东西。

感谢您的关注和问候。

标签： mysqlsubquery

Answer 1

在 MySQL 8+ 上，我们可以尝试使用ROW_NUMBERwith FIELD：

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id_book
                                 ORDER BY FIELD(lang, 'en', 'es') DESC) rn
    FROM e_rsts_test
    WHERE title IS NOT NULL
)

SELECT id, id_book, lang, title
FROM cte
WHERE rn = 1;

演示