如何使用sql查询对列表列表进行排序？

乔纳斯·陶林（Jonas Taulien）

抽象问题

我有一个sql-table，其中包含以下形式的记录：
(list_id, value)其中list_id是标识特定列表的整数，并且value是具有顺序的东西。

我现在很难编写一个sql查询，该查询返回该表的所有记录，这些记录首先按列表与其他列表的比较顺序排序，然后按排序value。

抽象的问题是，我想使用sql对列表列表进行排序。

比较两个列表的算法

比较两个列表的算法如下：

data CompareRes = FirstSmaller | FirstGreater | Equal deriving Show

compareLists :: Ord a => [a] -> [a] -> CompareRes
compareLists [] [] = Equal
-- Longer lists are considered to be smaller
compareLists _ [] = FirstSmaller
compareLists [] _ = FirstGreater
compareLists (x:xs) (y:ys) 
  | x < y = FirstSmaller
  | x > y = FirstGreater
  | otherwise = compareLists xs ys

细节

在我的具体情况下，值均为Date。所以我的桌子看起来像这样：

CREATE TABLE `list_date` (
  `list_id` INT  NOT NULL,
  `date`    DATE NOT NULL,
  PRIMARY KEY (`list_id`, `date`)
);

我正在使用mysql：8.0数据库，因此使用WINDOW-functions的解决方案是可以接受的。

例

数据

INSERT INTO `list_date` VALUES
   (1, '2019-11-02'), (1, '2019-11-03'), (1, '2019-11-04'), (1, '2019-11-05'), (1, '2019-11-07'), (1, '2019-11-08'), (1, '2019-11-09'),
   (2, '2019-11-01'), (2, '2019-11-03'), (2, '2019-11-04'),
   (3, '2019-11-01'), (3, '2019-11-02'), (3, '2019-11-03'),
   (4, '2019-11-02'), (4, '2019-11-04'), (4, '2019-11-13'), (4, '2019-11-14'),
   (5, '2019-11-03'), (5, '2019-11-04'), (5, '2019-11-05'), (5, '2019-11-10'),
   (6, '2019-11-01'), (6, '2019-11-02'), (6, '2019-11-03'), (6, '2019-11-05');

询问

我真正挣扎的地方是创建一个计算以下内容的表达式list_rank：

SELECT 
    `list_id`, 
    `date`,
    <PLEASE HELP> as `list_rank`
FROM 
    `list_date`
ORDER BY 
    `list_rank`, `date`;

预期结果

| list_id | date       | list_rank |
|---------|------------|-----------|
| 6       | 2019-11-01 | 1         |
| 6       | 2019-11-02 | 1         |
| 6       | 2019-11-03 | 1         |
| 6       | 2019-11-05 | 1         |
| 3       | 2019-11-01 | 2         |
| 3       | 2019-11-02 | 2         |
| 3       | 2019-11-03 | 2         |
| 2       | 2019-11-01 | 3         |
| 2       | 2019-11-03 | 3         |
| 2       | 2019-11-04 | 3         |
| 1       | 2019-11-02 | 4         |
| 1       | 2019-11-03 | 4         |
| 1       | 2019-11-04 | 4         |
| 1       | 2019-11-05 | 4         |
| 1       | 2019-11-07 | 4         |
| 1       | 2019-11-08 | 4         |
| 1       | 2019-11-09 | 4         |
| 4       | 2019-11-02 | 5         |
| 4       | 2019-11-04 | 5         |
| 4       | 2019-11-13 | 5         |
| 4       | 2019-11-14 | 5         |
| 5       | 2019-11-03 | 6         |
| 5       | 2019-11-04 | 6         |
| 5       | 2019-11-05 | 6         |
| 5       | 2019-11-10 | 6         |

要么

预期结果的视觉表示

该图像是我的应用程序产生的当前实时结果。当前，排序是使用Java实现的。

编辑

在没有得到更好的答案之后，我实现了@ gordon-linoff建议的解决方案：

SELECT 
    `list_id`, 
    `date`
FROM 
    `list_date`
        INNER JOIN (
            SELECT `sub`.`list_id`,
            GROUP_CONCAT(`sub`.`date` ORDER BY `sub`.`date` SEPARATOR '')  as `concat_dates`
            FROM `list_date` as `sub`
            GROUP BY `sub`.`list_id`
        ) `all_dates` ON (`all_dates`.`list_id` = `list_date`.`list_id`)
ORDER BY 
    `all_dates`.`concat_dates`, `date`;

我还创建了一个SQL Fiddle-这样您就可以试用您的解决方案。

但是此解决方案无法按预期对列表进行排序，因为较长的列表被认为大于较小的列表。

因此，我仍然希望收到能满足我100％要求的解决方案：）

戈登·利诺夫

如果我理解正确，则可以按并置的日期对列表进行排序：

select ld.*
from list_date ld join
     (select list_id, group_concat(date) as dates
      from ld
      group by list_id
     ) ldc
     on ld.list_id = ldc.list_id
order by ldc.dates, ld.date;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。