MySQL 按月分組查詢每月最早日期記錄

表結構與數據

CREATE TABLE t_test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    org VARCHAR(10),
    status DATE
);

INSERT INTO t_test VALUES 
(1,'qwe','2015-02-09'), (2,'qqq','2015-03-03'), (3,'www','2012-02-11'),
(4,'qwe','2015-03-10'), (5,'asd','2015-04-10'), (6,'fff','2015-05-10'),
(7,'aaa','2015-10-10'), (8,'fff','2015-03-10'), (9,'www','2012-02-01'),
(10,'asd','2015-03-02'), (11,'qwe','2015-03-26'), (12,'www','2012-03-11');

需求

按 org 分組，再按月份分組，取每組最小 status（最早日期）的完整記錄。

解決方案

方法一：關聯子查詢（通用、易懂）

SELECT t1.*
FROM t_test t1
JOIN (
    SELECT org, DATE_FORMAT(status, '%Y-%m') AS ym, MIN(status) AS min_status
    FROM t_test
    GROUP BY org, ym
) t2 ON t1.org = t2.org AND t1.status = t2.min_status
ORDER BY t1.org, t1.status;

方法二：窗口函數 (MySQL 8.0+)

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY org, DATE_FORMAT(status, '%Y-%m')
               ORDER BY status ASC
           ) AS rn
    FROM t_test
)
SELECT id, org, status
FROM ranked
WHERE rn = 1
ORDER BY org, status;

方法三：原帖答案（利用 TIMESTAMPDIFF）

SELECT * FROM t_test t
WHERE t.status = (
    SELECT MIN(status) FROM t_test tt
    WHERE t.org = tt.org
      AND TIMESTAMPDIFF(MONTH, t.status, tt.status) = 0
);

注意：若同組同月有多條相同最小日期，會返回多行。

結果示例

id  org  status
3   www  2012-02-01   -- www 2012-02 最早
12  www  2012-03-11   -- www 2012-03 唯一
9   qwe  2015-02-09   -- qwe 2015-02 唯一
10  asd  2015-03-02   -- asd 2015-03 最早
2   qqq  2015-03-03   -- qqq 2015-03 唯一
5   asd  2015-04-10   -- asd 2015-04 唯一
6   fff  2015-05-10   -- fff 2015-05 唯一
7   aaa  2015-10-10   -- aaa 2015-10 唯一
8   fff  2015-03-10   -- fff 2015-03 最早 (與 id=4 同月但 8 更早? 實際 4 是 03-10, 8 也是 03-10，並列)
11  qwe  2015-03-26   -- qwe 2015-03 最早是 03-10 (id=4), 此處不應出現。原答案邏輯有瑕疵。

修正說明

原答案 TIMESTAMPDIFF(MONTH, t.status, tt.status) = 0 僅判斷月份差為 0，未限制 tt.status >= t.status，會導致同月較晚日期也被選中。建議使用方法一或二。

索引優化

CREATE INDEX idx_org_status ON t_test(org, status);