mysql - 如何在 MySQL 上获取类似查询的历史记录？

Question

我需要一点帮助。

我正在 MySQL 中构建一个数据库，我将在其中进行一系列不同的事件。每个事件都是列表的一部分。

所以，我的数据库中有以下表格。

列表

• 编号
• 姓名

事件

• 编号
• 姓名
• idList(FK 到列表)

我还想知道每个事件何时完成(您可以多次完成相同的事件)。为此，我有另一个表:

历史

• 日期
• idActivity(FK 到事件)

当用户完成一个事件时，我将这个事件的 ID 和当前事件完成的时间添加到历史表中。

我想获取完整列表及其完成日期。当事件尚未完成时，我希望它显示日期为空。

但是，只获取一次列表很容易。一个简单的 Left Outer Join 就可以解决问题。我的问题是每次历史表上出现日期时我都想获取整个列表。

这就是我要找的:

列表:

id      |       name
1       |       list1

事件:

id      |       name        |       idList
1       |     Activity1     |         1
2       |     Activity2     |         1
3       |     Activity3     |         1
4       |     Activity4     |         1
5       |     Activity5     |         1
6       |     Activity6     |         1

历史:

date      |    idActivity       
17/07/14  |        1
17/07/14  |        3
17/07/14  |        4
17/07/14  |        6

16/07/14  |        2
16/07/14  |        3
16/07/14  |        5

预期结果:

idActivity     |     idList     |      activityName      |        date           
    1          |        1       |       Activity1        |      17/07/14
    2          |        1       |       Activity2        |        NULL
    3          |        1       |       Activity3        |      17/07/14
    4          |        1       |       Activity4        |      17/07/14
    5          |        1       |       Activity5        |        NULL
    6          |        1       |       Activity6        |      17/07/14

    1          |        1       |       Activity1        |        NULL
    2          |        1       |       Activity2        |      16/07/14
    3          |        1       |       Activity3        |      16/07/14
    4          |        1       |       Activity4        |        NULL
    5          |        1       |       Activity5        |      16/07/14
    6          |        1       |       Activity6        |        NULL

Answer 1

“技巧”是使用 CROSS JOIN(或半交叉连接)操作和来自 history 表的不同日期列表，以生成您要返回的行集。

然后对 history 表进行 LEFT JOIN(外连接)以查找匹配的历史记录行。

像这样:

SELECT a.id             AS idActivity
     , a.idList         AS idList
     , a.name           AS activityName
     , h.date           AS `date`
  FROM activity a
 CROSS
  JOIN ( SELECT s.date
           FROM history s
         GROUP BY s.date
       ) r
  LEFT
  JOIN history h
    ON h.idActivity = a.id
   AND h.date = r.date
 ORDER 
    BY r.date
     , a.id

该查询从 activity 中获取六行，从 history 中获取两行(date 的不同值)(内联 View 别名为 <强r)。 CROSS JOIN 操作将六行中的每一行与两行中的每一行进行匹配，以生成 12 行的笛卡尔积。

要获得按指定顺序返回的行，我们按 date 排序，然后按 activity.id 排序。