MySQL 複雜查詢：統計每個 Lead 最新狀態的月度分布

需求

表結構：lead_id, expected_date, update_date, status。
每個 lead 多條記錄，取**最新 update_date** 的那條，按其 expected_date 月份與 status 交叉統計。

解決思路

1. 子查詢/窗口函數獲取每個 lead 最新記錄。
2. 提取月份、狀態，條件聚合生成橫表。

MySQL 8.0+ 窗口函數寫法

WITH Latest AS (
    SELECT lead_id, expected_date, status,
           ROW_NUMBER() OVER (PARTITION BY lead_id ORDER BY update_date DESC) AS rn
    FROM leads
)
SELECT 
    status,
    SUM(CASE WHEN MONTH(expected_date)=3 THEN 1 ELSE 0 END) AS `3月`,
    SUM(CASE WHEN MONTH(expected_date)=4 THEN 1 ELSE 0 END) AS `4月`,
    SUM(CASE WHEN MONTH(expected_date)=5 THEN 1 ELSE 0 END) AS `5月`,
    SUM(CASE WHEN MONTH(expected_date)=6 THEN 1 ELSE 0 END) AS `6月`,
    SUM(CASE WHEN MONTH(expected_date)=7 THEN 1 ELSE 0 END) AS `7月`
FROM Latest
WHERE rn = 1
GROUP BY status
ORDER BY FIELD(status, '跟蹤', '談判中', '審核中');

MySQL 5.7 兼容寫法

SELECT 
    l.status,
    SUM(CASE WHEN MONTH(l.expected_date)=3 THEN 1 ELSE 0 END) AS `3月`,
    ...
FROM leads l
JOIN (
    SELECT lead_id, MAX(update_date) AS max_ud
    FROM leads GROUP BY lead_id
) latest ON l.lead_id = latest.lead_id AND l.update_date = latest.max_ud
GROUP BY l.status;

返回 JSON 給 Highcharts

後端將結果組裝為 {categories:['3月',...], series:[{name:'跟蹤', data:[0,0,...]},...]}，前端直接 chart.series[0].setData(...)。