mysql - 编写 mysql 查询的想法

Question

我有一个名为 wp_bp_activity 的表，其中包含许多列，我认为我应该使用其中的 4 个来解决这个问题:id、type、item_id 和 date_recorded。

1 - 当有人发布新事件时，type 是 activity_update 并且 item_id 是 0

2 - 当有人对事件发表新评论时，其type 为 activity_comment 且 item_id 已存在id

列中的事件 ID

3 - 在两者中，date_recorded 是插入数据的日期。

表格中还有更多的类型。

但我只想获取最近有人回复或的type为activity_update的行(基于date_recorded 我认为)

我试过的是:

SELECT  a.*, b.*
FROM wp_bp_activity as a, wp_bp_activity as b
WHERE
  ((b.type = 'activity_update') OR (b.type = 'activity_comment' AND b.item_id = a.id))
order by cast(a.date_recorded as datetime) desc
limit 0,20

执行时间太长，以内存不足错误结束。

感谢任何有关此类查询的帮助。

更新#1

                        wp_bp_activity table

    id              type             item_id         date_recorded
|---------|-----------------------|-----------|--------------------------
|  12081  |   activity_comment    |   12079   |    2013-10-18 07:27:01
|---------|-----------------------|-----------|--------------------------
|  12080  |   activity_update     |     0     |    2013-10-18 07:26:40
|---------|-----------------------|-----------|--------------------------
|  12079  |   activity_update     |     0     |    2013-10-17 05:15:43

我想从此表中的哪些行是按以下顺序排列的这些 ID:

12079
12080

我不想得到的:

activity_comment类型

应按什么顺序获取行？

如您所见，activity_comment 类型的行有一个 item_id，其值为 12079。所以12079是最近有人评论的最新事件，而12080没有评论，只是发布了。所以我想要两者，但顺序如下:

12079
12080

Answer 1

我假设您正在寻找“最近的条目”(WHERE type = 'activity_update' AND date_recorded > [threshold])和“有最近回复的条目，不管条目的年龄”(WHERE reply.type = 'activity_comment' AND reply.date_recorded > [threshold])。

第一组很简单:

SELECT entries.*
FROM activity AS entries
WHERE type = 'activity_update' AND date_recorded > [threshold]

第二组不太明显:

SELECT entries.*
FROM activity AS entries
JOIN activity AS replies
    ON replies.item_id = entries.id
    AND replies.type = 'activity_comment'
WHERE
    entries.type = 'activity_update'
    AND replies.date_recorded > [threshold]

综合起来:

SELECT entries.*
FROM activity AS entries
LEFT JOIN activity AS replies -- LEFT JOIN, because an INNER JOIN would filter out entries without any reply
    ON  replies.item_id = entries.id
    AND replies.type = 'activity_comment'
WHERE
    entries.type = 'activity_update'
    AND (
        entries.date_recorded > [threshold]
        OR replies.date_recorded > [threshold] -- evaluates as FALSE if replies.date_recorded is NULL
    )
ORDER BY IFNULL(replies.date_recorded, entries.date_recorded) -- replies if not null, entries otherwise

我并不为我表现不佳的 ORDER BY 子句感到骄傲，我希望有人能提出更好的想法