問題描述

我有一個名為 wp_bp_activity 的表，其中包含許多列，我認為我應該使用其中的 4 個來解決這個問題:id, type、item_id 和 date_recorded.

I've a table named wp_bp_activity with many columns that I think I should work with 4 of them for this issue: id, type, item_id and date_recorded.

1 - 當有人發布新活動時，type 是 activity_update 而 item_id 是 0

1 - When someone post a new activity the type is activity_update and item_id is 0

2 - 當有人對活動發表新評論時，其 type 是 activity_comment 并且 item_id 存在id

2 - When someone post a new comment on an activity its type is activity_comment and item_id is existed activity id in column id

3 - 在兩者中，date_recorded 是插入數據的日期.

3 - In both, date_recorded is the date of when data is inserted.

表中還有更多type.

但我只想獲取最近有人回復或的type的activity_update的行是新的(基于date_recorded 我認為)

But I wanna fetch only rows with type of activity_update that someone is recently replied to or are new (based on date_recorded I think)

我試過的是:

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

執行時間過長，并以內存不足錯誤結束.

That takes too long to be executed and ends with memory insufficient error.

對此類查詢的任何幫助表示贊賞.

Any help on this kind of query is appreciated.

更新 #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沒有評論只是發布.所以我想要兩個，但按這個順序:

As you can see the row with activity_comment type has an item_id with the value of 12079. so 12079 is the latest activity that recently someone made a comment on it, and 12080 has no comments but is just posted. So I want both but at this order:

12079
12080

推薦答案

我假設您正在尋找最近的條目"(WHERE type = 'activity_update' AND date_recorded > [threshold]) 和具有最近回復的條目，無論條目的年齡如何"(WHERE reply.type = 'activity_comment' AND reply.date_recorded > [threshold]).

I am going to assume that you are looking for "recent entries" (WHERE type = 'activity_update' AND date_recorded > [threshold]) and "entries having a recent reply, regardless of the entry's age" (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 子句表現不佳而自豪，我希望有人能提出更好的想法

I am not proud of my poorly performing ORDER BY clause, I hope someone can suggest a better idea

這篇關于編寫 mysql 查詢的想法的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！