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
精彩评论