sql - 多表有序 GROUP BY 或 Distinct SQL

Question

我一直在努力解决这个问题，但没有成功，所以如果有人能帮助我就太好了。

我有 2 张 table 。项目和项目标志。

项目

id        name       levelid        containertypeid        roomid        userid      timestamp
--        ----       -------        ---------------        ------        ------      -----
1         Item 1     0              0                      3             5           1365072108
2         Item 2     0              0                      3             5           1365342108
3         Item 3     0              0                      3             5           1389072108
4         Item 4     0              0                      3             5           1365085108
5         Item 5     2              3                      3             5           1365962108
6         Item 6     1              3                      3             5           1365322108

Item_Flags

id        itemid     flagid        userid        timestamp
--        ----       -------       -------       ---------
1         1          1              5            1365072108
2         1          5              5            1365342108
3         1          2              5            1389072108
4         1          3              5            1365085108
5         2          4              5            1365762108
6         2          1              5            1365921087
7         2          3              5            1365252108
8         3          6              5            1365982108
9         3          7              5            1365312108
10        3          8              5            1365342108

目标

我想要做的是获取所有项目的列表，按降序时间戳排序。因此，实际上，我想要一个基于我们更新实际项目或为项目添加标志时的最新时间戳的项目列表。每次针对特定项目添加标志或更新项目名称或级别时，应用程序都会更新时间戳。问题是，如果我想取回名称、levelid 等字段，我无法运行 GROUP BY，因为我在聚合函数中使用了它们，所以实际值不会返回。如果我不对它们进行分组，我显然会根据每个项目的标志数量得到多个结果。这是我的查询:

SELECT 
                items.id,
                items.name,   
                items.levelid,
                items.containertypeid,
                items.roomid,
                items.userid
        FROM 
                items
        LEFT OUTER JOIN item_flags ON
                items.id = flags.itemid
        WHERE
                items.levelid = 0 AND 
                items.containertypeid = 0 AND 
                items.room = 3 AND
                items.userid = 5
        GROUP BY 
               items.id
        ORDER BY 
               items.timemodified DESC
               item_flags.timemodified DESC

如果我将除 items.id 之外的所有选择字段包装在聚合函数中并完全删除 ORDER by ，我可以让它工作，但是当我取回它们时我需要使用这些字段并且我需要它们订购!此时我什至不确定查询中的逻辑是否有意义，因为虽然我说 flags.itemid 等于 items.id，但那里存在一对多关系。

我看过SQL Query - Grouping with multiple tables ，但那里没有 ORDER BY，我不知道如何让它工作!

使用 PG SQL。

谢谢!

==================================

事实证明，在这一切结束时，查询也必须与 MySQL 一起工作，而 With 显然不起作用。因此，我们想出了一个不同的解决方案，我正在对其进行测试，我会尽快发布，以防它对其他人有用。

Answer 1

你可以使用下面的 GROUP BY

GROUP BY
            items.id,
            items.name,   
            items.levelid,
            items.containertypeid,
            items.roomid,
            items.userid

由于 items.id 是唯一的，这将对记录进行分组，与“GROUP BY items.id”相同。

这对于数据库来说似乎需要更多的工作，但由数据库决定它是否可以检查分组的 ID。