MySQL 每日去重邏輯：若含 0 則取 0，否則取最大值

業務場景

同一天同一用戶有多筆記錄，infoStatus 欄位可能為 0 或正數。需求：

• 按 userid + 日期分組。
• 組內若存在 0，結果取 0；否則取最大值。

測試資料

+----+--------+---------------------+------------+
| id | userid | inputDate           | infoStatus |
+----+--------+---------------------+------------+
| 1  | 1      | 2014-07-11 00:00:00 | 20013      |
| 2  | 1      | 2014-07-11 00:00:00 | 0          |
| 3  | 2      | 2014-07-12 00:00:11 | 20015      |
| 4  | 2      | 2014-07-12 00:00:22 | 20013      |
| 5  | 2      | 2014-07-14 00:00:00 | 0          |
| 6  | 2      | 2014-07-15 00:00:00 | 20013      |
| 7  | 2      | 2014-07-15 00:00:00 | 0          |
| 8  | 2      | 2014-07-16 00:00:00 | 0          |
| 9  | 2      | 2014-07-13 00:00:00 | 20013      |
| 10 | 2      | 2014-07-13 00:00:00 | 0          |
| 11 | 2      | 2014-07-14 00:00:00 | 0          |
+----+--------+---------------------+------------+

解決方案：條件聚合

SELECT
    MIN(id) AS id,                 -- 取組內最小 id 作為代表
    userid,
    DATE(inputDate) AS `date`,
    MAX(infoStatus) AS max_status,
    MIN(infoStatus) AS min_status,
    CASE WHEN MIN(infoStatus) = 0 THEN 0 ELSE MAX(infoStatus) END AS final_status
FROM t2
WHERE infoStatus = 0 OR infoStatus <= 99999  -- 過濾異常值
GROUP BY userid, DATE(inputDate);

執行結果

+----+--------+------------+-------------+------------+--------------+
| id | userid | date       | max_status  | min_status | final_status |
+----+--------+------------+-------------+------------+--------------+
| 1  | 1      | 2014-07-11 | 20013       | 0          | 0            |
| 3  | 2      | 2014-07-12 | 20015       | 20013      | 20015        |
| 9  | 2      | 2014-07-13 | 20013       | 0          | 0            |
| 5  | 2      | 2014-07-14 | 0           | 0          | 0            |
| 6  | 2      | 2014-07-15 | 20013       | 0          | 0            |
| 8  | 2      | 2014-07-16 | 0           | 0          | 0            |
+----+--------+------------+-------------+------------+--------------+

進階：窗口函數寫法

MySQL 8.0+ 可用 ROW_NUMBER() 保留完整欄位：

WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY userid, DATE(inputDate)
                           ORDER BY CASE WHEN infoStatus=0 THEN 0 ELSE 1 END, infoStatus DESC) AS rn
    FROM t2
    WHERE infoStatus = 0 OR infoStatus <= 99999
)
SELECT id, userid, DATE(inputDate) AS `date`, infoStatus AS final_status
FROM ranked
WHERE rn = 1;

Excel 對應技巧

• 去重保留最大值：輔助欄 =COUNTIF($A$1:A1,A1)，篩選 =1 的列。
• 陣列公式去重且排除 0：=T(INDEX(A:A,MATCH(,0/IF(COUNTIF(B$1:B1,A$1:A$1000)=0,ROW(A$1:A$1000)),))) (Ctrl+Shift+Enter)。