我有一個 MySQL dbusers表，其中有一列名為lastLoginthis 只是一個簡單的時間戳，用于指示用戶上次登錄系統的時間。
例如
id -- name -- lastLogin -- accountId
2. bob. 1639572638 4
3. tim. 1639572638 4
3. ant. 1639572638 5
4. leroy. 1339572638 6
預期成績
accountId -- activeUsers
4 2
5 1
6 0
我當前的查詢回傳 0 行但不知道為什么
SELECT accountId, from_unixtime(lastLogin) as lastlogin, count(distinct(id)) as activeUsers
FROM user
HAVING lastlogin > now() - INTERVAL 30 day
ORDER BY lastlogin desc;
uj5u.com熱心網友回復：
SELECT accountId,
FROM_UNIXTIME(MAX(lastlogin)) lastlogin, -- not listed in desired output
-- but present in the query
SUM(lastlogin > UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 30 DAY)) activeUsers
FROM user
GROUP BY accountId
供distinct id使用
SELECT accountId,
FROM_UNIXTIME(MAX(lastlogin)) lastlogin,
COUNT(DISTINCT CASE WHEN lastlogin > UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 30 DAY) THEN id END) activeUsers
FROM user
GROUP BY accountId
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f754e9ed49d872d0d68173a803f96126
uj5u.com熱心網友回復：
試試這個：
with u as
(select accountId, count(*) as activeUsers from user
group by accountId
having FROM_UNIXTIME(max(lastlogin)) > now() - INTERVAL 30 day),
v as
(select distinct accountId from user)
(select v.accountId, coalesce(u.activeUsers, 0) as activeUsers from v left join
u on v.accountId = u.accountId)
小提琴
uj5u.com熱心網友回復：
好吧，我想出來了，希望能幫助別人-
SELECT accountId,count(distinct(id)) as activeUsers
FROM user
WHERE FROM_UNIXTIME(lastlogin) > now() - INTERVAL 30 day
GROUP BY accountId;
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/387372.html