SQL查询：用户关注的过去24小时内发布的评论数

I've got a site where users follow users they like and can read their latest updates.

I'd like to show my users a simple count of how many comments the people they follow have posted in the last 24 hours. I'm having trouble figuring out the query.

I have two tables.

comment

• cid (comment id)
• timestamp
• uid (id of person who posted the comment)
• comment (content of the comment)

user_relationships

• requester_id (id of the user who followed the person)
• requestee_id (id of the person who was followed)

In plain english, I think the query is something like this:

COUNT the cid.comment from the comments table where uid.comment is equal to requestee_id.user_relationships associated with requester_id.user_relationships value of X.

I need to get all the UIDs of people being followed by a given UID from the usr_relationship table. Then count up how many comments those people have posted in the last 24 hours and spit that number out.

What would this query look like?

    SELECT COUNT(*)
      FROM Comment
     WHERE uid in (SELECT user_relationships.requesterid
                     FROM user_relationships
                    WHERE user_relationships.requesteeid = @ThisID)

Hope this helps, Cheers,

using a join

SELECT
    COUNT(c.cid)
FROM
    user_relationships r
    INNER JOIN comments c ON c.uid=r.requester_id AND c.timestamp > NOW() - INTERVAL 24 HOUR
WHERE
    r.requestee_id={user_id}

join is faster

SELECT COUNT(comments.uid) FROM comments JOIN user_relationships ON user_relationships.requestee_id = comments.uid WHERE user_relationships.requester_id = $some_id

SELECT COUNT(1) 
FROM user_relationships ur 
JOIN comment c ON (ur.requestee_id = c.uid)
WHERE c.timestamp >= (CURRENT_TIMESTAMP - <interval of 1day>) 
  AND ur.requester_id = <visitor>

Also hope this helps.

You'll have to figure out how to create an interval to subtract from the timestamp.