查找一組使用者的 Max() 更新時間

數據庫是 MariaDB 10.3.25

我有 2 列相關：

使用者 ID 和 LastUpdate

UserID 由 user@domain LastUpdate 是一個日期欄位。

這是我的問題 - 我有目前的查詢：

select a.UserID, 
substring_index(a.UserID, '@', -1), 
max(a.lastupdate) 
from MyTable a 
group by a.UserID
having max(a.lastupdate) < '2020-03-31' 

這顯示了一年多內未更新的所有使用者和域。但是，我要考慮以下情況：

UserID LastUpdate

A@A.com 2020-08-16

B@A.com 2019-05-16

C@A.com 2021-05-05

使用目前查詢，將擷取 B@A.com，因此域 A.com 將被擷取為未使用，但使用者 C@A.com 仍處於活動狀態 - 我想做一個 Max(a.lastupdate ) 但將其分組， substring_index(a.UserID, '@', -1) 但也會吐出該域的所有使用者。我敢肯定，答案讓我眼前一亮……

最好的方法可能是以下。該解決方案利用了GENERATED COLUMNMariaDB >= 10.3（也在 MySQL >= 5.7 中）的功能——對於這樣的查詢非常方便——它還使查詢更具可讀性。

下面的所有程式碼都可以在小提琴上找到：

CREATE TABLE login
(
 user_id VARCHAR (255) NOT NULL PRIMARY KEY,
 last_login TIMESTAMP NOT NULL,
 domain_name VARCHAR (255) 
   GENERATED ALWAYS AS ((SUBSTRING_INDEX(user_id, '@', -1))) VIRTUAL,  -- can be STORED

 INDEX (last_login),  -- indexing up to you if you have lots of data...
 INDEX (domain_name)
);

然後是一些範例記錄：

INSERT INTO login (user_id, last_login) 
VALUES 
('pol@blah.ie', '2021-03-06'), 
('xyz@blah.ie', '2021-03-06'), 
('abc@blah.ie', '2021-03-06'), 
('pqr@blah.ie', '2021-03-06'), 
('rst@blah.ie', '2021-03-06'),
('fred@xyz.ie', '2020-03-20'), 
('bill@xyz.ie', '2020-03-15'), 
('mary@xyz.ie', '2020-03-14'), 
('joe@xyz.ie', '2020-02-12'), 
('mike@xyz.ie', '2020-01-31');

然後執行查詢：

SELECT * FROM 
(
 SELECT domain_name, MAX(last_login) AS last_login_by_domain
 FROM login
 GROUP BY domain_name
) AS tab
WHERE last_login_by_domain < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
ORDER BY last_login_by_domain DESC;

-- DESC because you might want to deal with recent ones more urgently?
-- renew subscription... whatever  

結果：

domain_name   last_login_by_domain       
    xyz.ie    2020-03-20 00:00:00

這適用於來自 dbfiddle.uk（一個很好的資源）的 MariaDB >= 10.3 的所有版本以及 MySQL >= 5.7 的版本。

它也適用於 MySQL 的 ONLY_FULL_GROUP_BY 設置！這非常重要 - 如果未設置該變數，則查詢可能（並且**將**- 也將在此處和此處查看）返回錯誤結果（請參閱我對此處對該問題的其他答案的評論）。

舉個例子，看看小提琴的底部，我已經指出了在這個（相對簡單的）案例中另一個答案出現的問題——在一個複雜的陳述中，這個問題可能導致各種難以發現錯誤 - 警告購買者！

編輯：查看對問題的評論後（小提琴）：

SELECT user_id 
FROM login
WHERE domain_name IN
(
 SELECT domain_name FROM
 (
   SELECT 
     domain_name, MAX(last_login) AS last_login_by_domain
   FROM login
   GROUP BY domain_name
 ) AS tab
 WHERE last_login_by_domain < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
);

結果：

user_id
bill@xyz.ie
fred@xyz.ie
joe@xyz.ie
mary@xyz.ie
mike@xyz.ie

引用自：https://dba.stackexchange.com/questions/291005