php - SQL排序多个数据行而不合并行

Question

对于通知系统，我试图获取多行完全独立的数据，并使用 unix 时间戳对它们进行排序。每一行的数据都是使用用户 USERID 选择的，然后这些行应该从它们发生的时间开始排序。

例如，被选中的表格之一是:

- USERID   msgid    timestamp
-   3        5       1234567

选择所有相关数据后的最终表格应如下所示:

- USERID    msgid    postid    playerpostid     friendrequestid     timestamp  
-   3         5                                                      1234567
-   3                  5                                             1234566
-   3        1234                                                    1234565
-   3                               542                              1234564

如您所见，所有表都组合在一起，但唯一连接它们的是 USERID，并按时间戳排序。

我试过加入，不幸的是，结果乘以了一个荒谬的结果。

Answer 1

SELECT * FROM (
  SELECT 
    USERID, msgid, NULL AS postid, NULL AS playerpostid, NULL AS friendrequestid, timestamp
  FROM messages
  WHERE USERID=3

  UNION ALL

  SELECT 
    USERID, NULL AS msgid, postid, NULL AS playerpostid, NULL AS friendrequestid, timestamp
  FROM posts
  WHERE USERID=3

  UNION ALL

  SELECT 
    USERID, NULL AS msgid, NULL AS postid, playerpostid, NULL AS friendrequestid, timestamp
  FROM playerposts
  WHERE USERID=3

  UNION ALL

  SELECT 
    USERID, NULL AS msgid, NULL AS postid, NULL playerpostid, friendrequestid, timestamp
  FROM friendrequests
  WHERE USERID=3
) AS baseview
ORDER BY timestamp