您(mysql)能按可选值和不同表中的值排序吗？

bq9c1y66 于 2021-06-15 发布在 Mysql

关注(0)|答案(1)|浏览(309)

因此，我有一个相当基本的基于php的网站，它使用mysql的一些数据。
我目前需要以一种依赖于其他表中可选字段的方式对一行mysql结果进行排序，我想知道这是否“容易”实现。
以下是数据库的外观（排序）：
名为“main”的主表

| 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个值可以按以下方式排序：
main.starttime（根据某些用户操作进行更新）
attachments.timestamp（用户可以随时添加附件）
revisions.timestamp（用户可以编辑主行，增量作为新行写入修订）
或（如果上述字段均不存在或为零）
main.timestamp（使用now（）创建行的默认日期时间）
我尝试在order by子句中使用greatest（），这很管用，但只适用于main.timestamp和main.starttime—不适用于我需要考虑的其他两个值。
这是我当前的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";

或使用：

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

我面临的主要问题是，并非每一行“main”在“attachments”或“revisions”中都有对应的行，但有些行有多个。
上面的例子应该分类为

2 > 3 > 1

如果分类可以的话。
希望你们能帮忙！

mysql php pdo sorting

来源：https://stackoverflow.com/questions/53887086/can-you-mysql-order-by-values-that-are-optional-and-in-different-tables

1条答案

按热度按时间

9lowa7mx1#

有点棘手，但很管用：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分组，因此在db fiddle中可以看到4行。
我使用了第二个查询来 Package 最后一个查询 MAX(updated_at) AS 'updated_at' 获取最大的分组查询（您可以看到 AS MAIN 正在做一个 GROUP BY MAIN.id ，这就是为什么我们使用 MAX 而不是 GREATEST ，最后一个获取字段列表，而不是分组字段）。
最后，我们通过查询和tada订购！
希望有帮助。

赞(0）回复(0）举报 2021-06-16

我来回答

您(mysql)能按可选值和不同表中的值排序吗？

1条答案

相关问题

热门标签

最新问答