php - 你(MySQL)可以按可选的值和不同表中的值进行排序吗？

Question

所以我有一个相当基本的基于 PHP 的网站，它使用 MySQL 来存储一些数据。

我目前需要以依赖于其他表中的可选字段的方式对一行 MySQL 结果进行排序，我想知道这是否可以“轻松”实现。

这是数据库的外观(有点):

主表称为“主”

    | id |  timestamp |  starttime |
    |  1 | 2010-06-10 | 2010-08-15 |
    |  2 | 2011-01-15 | 2011-03-12 |
    |  3 | 2011-01-27 | 0000-00-00 |

附加表称为“附件”

    | aid |  timestamp | id |
    |   1 | 2012-09-04 |  3 |
    |   2 | 2017-04-01 |  3 |

修订表称为“修订”

    | rid |  timestamp | id |
    |   1 | 2014-10-10 |  1 |
    |   2 | 2018-12-25 |  2 |

主表是基本需要的，附件和修订对于我想要提供的有序列表并不重要。

现在我希望能够按“上次更改日期”对其进行排序，这是一个问题，因为有 4 个值可以按以下顺序排序:

1. Main.starttime(根据某些用户操作更新)
2. Attachments.timestamp(用户可以随时添加附件)
3. Revisions.timestamp(用户可以编辑其主行，增量将作为新行写入修订)

或(如果以上字段都不存在或为零)

Main.timestamp(这是使用 NOW() 创建行的默认日期时间)

我尝试在 ORDER BY 子句中使用 GREATEST()，这有效，但仅适用于 Main.timestamp 和 Main.starttime - 不适用于我需要考虑的其他 2 个值。

这是我当前的 SQL 命令:

SELECT Main.*, User.lastname, User.surname, User.title
FROM Main, User
WHERE User.uid = Main.uid AND Main.state != 0
ORDER BY $orderby LIMIT :limit OFFSET :offset

我的 $orderby 变量是通过 PHP 处理的，因此它已被清理，但基本上可以是这样的:

$orderby = "Main.timestamp DESC";

或者使用GREATEST():

$orderby = "GREATEST(Main.timestamp, Main.starttime) DESC";

我面临的主要问题是，并非“主”的每一行在“附件”或“修订”中都有对应的行，但有些有多个。

上面的例子应该排序为

2 > 3 > 1

排序是否有效。

希望大家帮忙!

Answer 1

有点棘手，但它有效:https://www.db-fiddle.com/f/bUV99RzrxcHkgFyUWUMGEe/0

查询

SELECT
    MAIN.id,
    MAX(MAIN.updated_at) AS 'updated_at'
FROM (
  SELECT
    M.id,
    GREATEST(
          IFNULL(M.`timestamp`, '1970-01-01 00:00:00'),
          IFNULL(M.starttime, '1970-01-01 00:00:00'),
          IFNULL(A.`timestamp`, '1970-01-01 00:00:00'),
          IFNULL(R.`timestamp`, '1970-01-01 00:00:00')
    ) AS 'updated_at'
  FROM
    main M
  LEFT JOIN
    attachment A ON M.id = A.id
  LEFT JOIN
    revision R ON M.id = R.id
) AS MAIN
GROUP BY
    MAIN.id
ORDER BY
    updated_at DESC

说明

我使用第一个查询来选择您的所有主要内容及其相关附件和修订版本。然后，对于每个字段，我计算了一个 updated_at 字段，该字段取多个字段之间的最大值(注意:对于空值来说，最大值效果不佳，因此 IFNULL(date, '1970- 01-01 00:00:00)，因此它假设主时间戳、主开始时间、附件时间戳和修订时间戳之间至少有一个日期不为空)。

此时，行未按主 ID 分组，因此您会在数据库 fiddle 中看到 4 行。

我使用了第二个查询来包装最后一个查询，并使用 MAX(updated_at) AS 'updated_at' 来获取分组查询的最大值(您会看到名为 AS MAIN 的包装查询正在执行 GROUP BY MAIN.id，这就是为什么我们使用 MAX 而不是 GREATEST，最后一个采用字段列表而不是分组字段)。

最后，我们根据查询进行排序!

希望有帮助。