开发者

Complex SQL query... 3 tables and need the most popular in the last 24 hours using timestamps

I have 3 tables with a column in each which relates to one ID per row. I am looking for an sql statement query which will check all 3 tables for any rows in the last 24 hours (86400 seconds) i have stored timestamps in each tables under column time.

After I get this query I will be able to do the next step which is to then check to see how many of the ID's a reoccurring so I can then sort by most popular开发者_如何学JAVA in the array and limit it to the top 5...

Any ideas welcome! :)

Thanks in advance.

Stefan


SELECT  id, COUNT(*) AS cnt
FROM    (
        SELECT  id
        FROM    table1
        WHERE   time >= NOW() - INTERVAL 1 DAY
        UNION ALL
        SELECT  id
        FROM    table2
        WHERE   time >= NOW() - INTERVAL 1 DAY
        UNION ALL
        SELECT  id
        FROM    table3
        WHERE   time >= NOW() - INTERVAL 1 DAY
        ) q
GROUP BY
        id
ORDER BY
        cnt DESC
LIMIT 5
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜