SQL - Retrieve values in a single query with multiple where clauses
I have a table with the following data:
UserName | LastLogin
-------------------------------
User1 | 2010-10-25 10:05:47
User2 | 2010-10-23 11:10:27
User3 | 2010-10-12 05:39:34
User4 | 2010-10-20 12:22:11
User5 | 2010-09-17 08:41:05
I want to be able to run a query to get the number of people who have logged in in the last开发者_开发问答 3 days, last 7 days, and last 21 days (I know these numbers will overlap). I know I can get each particular value by running a query like (syntax may not be 100% correct):
SELECT COUNT(*)
FROM login
WHERE LastLogin >= DATEDIFF(NOW(), LastLogin, INTERVAL 3 DAY);
Can I run a query to return all three values in one query? Will a GROUP BY work, or can I use nested queries? Is it just as efficient to run the query three times with the different interval specified?
Use:
SELECT SUM(CASE WHEN l.lastlogin >= DATE_SUB(CURRENT_DATE, INTERVAL 3 DAY) THEN 1 ELSE 0 END) AS within_3,
SUM(CASE WHEN l.lastlogin >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) THEN 1 ELSE 0 END) AS within_7,
SUM(CASE WHEN l.lastlogin >= DATE_SUB(CURRENT_DATE, INTERVAL 21 DAY) THEN 1 ELSE 0 END) AS within_21
FROM LOGIN l
I used CURRENT_DATE rather than NOW() because NOW() includes the time portion.
SELECT *
FROM (SELECT COUNT(*) AS Last3Days FROM login WHERE LastLogin >= DATEDIFF(CURRENT_DATE, LastLogin, INTERVAL 3 DAY) a,
(SELECT COUNT(*) AS Last7Days FROM login WHERE LastLogin >= DATEDIFF(CURRENT_DATE, LastLogin, INTERVAL 7 DAY) b,
(SELECT COUNT(*) AS Last21Days FROM login WHERE LastLogin >= DATEDIFF(CURRENT_DATE, LastLogin, INTERVAL 21 DAY) c
精彩评论