MySQL数据库:查询表，并与其他表的“有效”日期列连接。

I cannot come up with a valid SQL Query for my problem, which I will describe in the scenario of a Blog. I have three tables: User, Blog – for blog entries, and UserStatus which holds the users' status he is assigned.

我无法为我的问题找到一个有效的SQL查询，我将在博客的场景中描述这个问题。我有三个表:用户、博客、博客条目和用户状态，这些都是用户的身份。

The tables look like that:

表格看起来是这样的:

User
ID | Name

Blog
ID | User_ID | Date | Text

UserStatus
ID | User_ID | Valid_From_Date | Status

I guess you can imagine what entries of User and Blog would look like. Here is how UserStatus could look for one user:

我想你可以想象一下用户和博客的条目会是什么样子。以下是用户状态如何查找一个用户:

ID | User_ID | Valid_From_Date | Status
34 |  7      |  2012-01-01     | Basic
35 |  7      |  2013-04-01     | Premium
36 |  7      |  2014-08-01     | Gold

The user's valid status at a certain date is the most recent one which satisfies Valid_From_Date<=Date. So on '2014-03-30' the valid status of this user is 'Premium'.

用户在某个日期的有效状态是最新的，它满足Valid_From_Date<=日期。因此，在“2014-03-30”这个用户的有效状态是“Premium”。

Now, what I want is to get all blog entries together with the users' names and valid status.

现在，我想要的是把所有的博客条目和用户的名字和有效的状态联系起来。

I have this approach:

我有这种方法:

SELECT User.Name, UserStatus.Status, Blog.Date, Blog.Text
FROM Blog
JOIN User ON User.ID = Blog.User_ID
JOIN UserStatus ON User.ID = UserStatus.User_ID
JOIN (Select User_ID, max(Valid_From_Date) AS date_for_most_recent_status FROM UserStatus
     WHERE date_for_most_recent_status <= ??? GROUP BY User_ID) AS recent_user_status
ON recent_user_status.User_ID = UserStatus.User_ID
AND date_for_most_recent_status = UserStatus.Valid_From_Date

??? -> Can I relate to the particular Blog.Date of current entry when joining?

? ? ?->我能与这个特别的博客联系吗?何时加入?

And that approach:

和这种方法:

SELECT User.Name, UserStatus.Status, Blog.Date, Blog.Text, max(Valid_From)
FROM Blog
JOIN User ON User.ID = Blog.User_ID
JOIN UserStatus ON User.ID = UserStatus.User_ID
WHERE UserStatus.Valid_From_Date <= Blog.Date 
GROUP BY Blog.Date, User.Name, Blog.Text

Here the good thing is that I can relate to the actual Blog.Date since it is just on Select-Statement. However, I don't know how to handle UserStatus.Status, which should be in the GROUP BY expression but cannot be, since I just want the most recent one.

这里的好处是我可以与实际的博客联系起来。日期，因为它只是在选择声明。但是，我不知道如何处理用户状态。状态，应该在组中，但不能，因为我只想要最近的一个。

Can anyone help me out here, please?

有人能帮帮我吗?

2 个解决方案

#1

Correlated subquery taking the value of a column in a table and mapping it to a value in a sub table. In this case we know we want the max valid_from_Date for each user so we use the userID from an table outside the subequery and the userID on the table inside the subquery and return just the max and using that as the criteria to determine which user status record to limit by on the join.

关联子查询获取表中列的值，并将其映射到子表中的值。在这种情况下,我们知道我们想要为每个用户马克斯valid_from_Date subequery外使用的用户id表和用户标识内的表子查询并返回马克斯和使用,作为标准来确定哪些用户状态记录限制连接。

SELECT User.Name, UserStatus.Status, Blog.Date, Blog.Text
FROM Blog
JOIN User 
  ON User.ID = Blog.User_ID
JOIN UserStatus 
  ON User.ID = UserStatus.User_ID
  and Valid_from_date = (Select max(Valid_From_Date)
      FROM UserStatus
      where user_ID = User.ID
      and UserStatus.Valid_from_Date <= Blog.Date)

#2

If you want data user wise then use below:

如果你想要数据用户明智的使用下面:

SELECT User.Name, a.Status, Blog.Date, Blog.Text, a.valid_from_date
FROM Blog
JOIN USER ON User.ID = Blog.User_ID
JOIN
(SELECT user_id,`status`,valid_from_date FROM userstatus ORDER BY valid_from_date DESC) a
ON a.user_id=User.ID
WHERE a.Valid_From_Date <= Blog.Date 
GROUP BY a.user_id;

If you want blog date, user, text wise then use:

如果你想要博客的日期，用户，文字的智慧然后使用:

SELECT User.Name, a.Status, Blog.Date, Blog.Text, a.valid_from_date
FROM Blog
JOIN USER ON User.ID = Blog.User_ID
JOIN
(SELECT user_id,`status`,valid_from_date FROM userstatus ORDER BY valid_from_date DESC) a
ON a.user_id=User.ID
WHERE a.Valid_From_Date <= Blog.Date 
GROUP BY Blog.Date, a.user_id, Blog.Text;