基于另一个表的时间戳选择MySQL信息

Question

基于另一个表的时间戳选择MySQL信息

7

编辑：我将此放在PHP和MySQL类别中，因为我认为可能有一种使用多个查询并使用PHP将它们绑定在一起的方法。我不想这样做...但说到底，解决方案就是解决方案...

希望有人能够轻松地思考这个问题，但我确实被卡住了！在我的网站上，我允许用户发布信息，并且他们可以随时更改会员资格。我想根据他们发布每篇信息时的会员身份运行一些统计数据，而不仅仅是他们当前的会员身份。例如，我想看到每个会员发布了多少信息。现在，我可以运行一个计数并加入用户、信息源和会员表，但这会按照他们当前的会员身份计算每个用户发布的所有信息，而这并不一定是他们发布时的会员身份。我希望这很清楚。

由于我们的数据库中已经有许多信息源，所以我无法添加一列显示发布者的会员类型。这是我的表（缩写）看看是否有任何查询想法可以做到这一点：

用户表

id  username  membershipid
1   John Doe  1

会员表

id  membershipname
1   Membership #1
2   Membership #2
3   Membership #3

会员历史记录表

id  membershipsid  usersid  unix_timestamp
1   1              1        1476635544.33
2   2              1        1476641890.11
3   3              1        1476642124.2
4   1              1        1476642161.51

订阅源表格

id  unix_timestamp     usersid
1   1476641716.809361  1
2   1476641783.866863  1
3   1476641822.779324  1
4   1476641904.066237  1
5   1476641973.767174  1
6   1476642182.821472  1

使用unix_timestamps很难快速查看...但我希望的是找到一个解决方案，能够提供以下内容：

按会员表统计的Feed数量

membershipid  feedcount
1             4
2             2
3             0

到目前为止，我尝试了许多方法，但它们最终都只提供用户当前的会员身份，例如：

SELECT
    a.MembershipName MembershipName,
    COUNT(*) Feeds
FROM (SELECT
        m.membership_name MembershipName
    FROM feeds f
    JOIN users u ON f.usersid = u.id
        JOIN memberships m ON u.membership_id = m.id
    GROUP BY f.id
    ORDER BY f.unix_timestamp DESC) a
    GROUP BY a.MembershipName
ORDER BY a.MembershipName

但这与会员历史记录表无关，因此我的输出表格是：

按会员计数的订阅表格

membershipid  feedcount
1             6
2             0
3             0

上面的表格显示，正确的应该是1->4，2->2和3->0。有人有什么想法吗？

- Ridge Robinson

类似于你的情况：https://dev59.com/q2855IYBdhLWcg3w7pCg 他们的classification表将是你的会员历史记录表，而他们的closed_cases表则是你的动态表。 - user5051310

这是一个相当类似的问题...我要看看是否能够复制它。但我遇到的一个问题是，他的解决方案只是通过添加一个“endtime”新列来解决的。我希望我的结束时间是该用户在memberships_history表中的下一个值。 - Ridge Robinson

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Johnny · Accepted Answer

注意事项#1-您想执行的查询非常昂贵，我的建议是在发布Feed时将当前的membershipsid保存到feeds表中。

注意事项#2-下面的查询假定成员资格历史记录始终至少包含每个用户的一个条目，而不仅仅是在更改时（因此第一次分配它时创建了历史记录）。

我认为您的解决方案的方式是“如何在X时间查询会员身份ID值？”，答案很简单，它是在X时间之前最近更改的值：

SELECT h.membershipsid
FROM membershipshistory h
WHERE h.usersid = {USERID} AND h.unix_timestamp < {X}
ORDER BY h.unix_timestamp DESC
LIMIT 1

下一步是“如何列出所有的订阅以及成员ID，其中X是发布订阅的时间？”

SELECT
  feeds.id,
  feeds.usersid,
  (
    SELECT h.membershipsid
    FROM membershipshistory h
    WHERE h.usersid = feeds.usersid AND h.unix_timestamp < feeds.unix_timestamp
    ORDER BY h.unix_timestamp DESC
    LIMIT 1
  ) AS historical_membershipsid
FROM
  feeds

输出结果（来自我的样本数据）：

+------+---------+--------------------------+
| id   | usersid | historical_membershipsid |
+------+---------+--------------------------+
|    1 |       1 |                        1 |
|    2 |       1 |                        2 |
|    3 |       1 |                        3 |
+------+---------+--------------------------+

从这个问题的解决方案应该很简单，只需将整个查询放入视图或子查询中，并按历史会员身份分组，但请记住，这非常昂贵。

更新

如果最终解决方案不那么明显，我很抱歉，以下是使用历史数据计算每个会员的供稿数量的最终查询：

SELECT
  hf.historical_membershipsid AS membershipsid,
  COUNT(hf.id) AS feedcount
FROM (
  SELECT
    feeds.id,
    feeds.usersid,
    (
      SELECT h.membershipsid
      FROM membershipshistory h
      WHERE h.usersid = feeds.usersid AND h.unix_timestamp < feeds.unix_timestamp
      ORDER BY h.unix_timestamp DESC
      LIMIT 1
    ) AS historical_membershipsid
  FROM
    feeds) AS hf
GROUP BY
  hf.id