如何获得类似MySQL查询的历史?

I'd like a little help here.

我需要你帮忙。

I'm building a database in MySQL where I will have a bunch of different activities. Each activity is part of a list.

我在MySQL中建立一个数据库我将会有很多不同的活动。每个活动都是列表的一部分。

So, I have the following tables on my database.

因此，我的数据库中有以下表。

List

列表

id
id
name
的名字

Activity

活动

id
id
name
的名字
idList (FK to List)
idList(颗列表)

I also want to know when each activity is finished (you can finish the same activity many times). To accomplish that, I have another table:

我还想知道每个活动何时结束(您可以多次完成相同的活动)。为了实现这一点，我有另一个表格:

History

历史

date
日期
idActivity (FK to activity)
idActivity(颗活动)

When the user finishes an activity, I add the id of this activity and the current time the activity was finished, to the History table.

当用户完成一个活动时，我将该活动的id和活动完成的当前时间添加到History表。

I want to get the entire list with the date it was finished. When an activity has not been finished, I want it to show the date as null.

我想要得到完整的列表，以及它完成的日期。当一个活动尚未完成时，我希望它将日期显示为null。

But, getting the list just once is easy. A simple Left Outer Join will do the trick. My issue here is that I want to get the ENTIRE list everytime a date appears on the history table.

但是，只获得一次列表是很容易的。简单的左外连接就可以了。我这里的问题是，每当历史表上出现一个日期时，我都希望获得整个列表。

This is what I'm looking for:

这就是我要找的:

List:

列表:

id      |       name
1       |       list1

Activity:

活动:

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

History:

历史:

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

Expected Result:

预期结果:

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

1 个解决方案

#1

The "trick" is to use a CROSS JOIN (or semi-cross join) operation with a distinct list of dates from the history table, to produce the set of rows you want to return.

“技巧”是使用一个交叉连接(或半交叉连接)操作，其中有一个来自历史表的不同日期列表，以生成您想要返回的行集。

Then a LEFT JOIN (outer join) to the history table to find the matching history rows.

然后将一个左连接(外部连接)放到历史表中，以找到匹配的历史行。

Something like this:

是这样的:

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

That query gets the six rows from activity, and two rows (distinct values of date) from history (inline view aliased as r). The CROSS JOIN operation matches each of the six rows with each of the two rows, to produce a Cartesian product of 12 rows.

该查询从活动中获取6行，并且从历史(内联视图别名为r)的两行(不同的日期)。交叉连接操作将每一行与这两行中的每一行匹配，生成12行的笛卡尔积。

To get the rows returned in the specified order, we order by date, and then by activity.id.

为了以指定的顺序返回行，我们按日期排序，然后按activity.id排序。

#1