場景

訂單表 orders 有 total（金額）與 createtime（日期時間），需統計每日/每月總額。

MSSQL 寫法

按日統計

SELECT SUM(total) AS daily_total,
       CONVERT(CHAR(10), createtime, 120) AS day
FROM orders
GROUP BY CONVERT(CHAR(10), createtime, 120)
ORDER BY day;

按月統計

SELECT SUM(total) AS monthly_total,
       CONVERT(VARCHAR(7), createtime, 120) AS month
FROM orders
GROUP BY CONVERT(VARCHAR(7), createtime, 120)
ORDER BY month;

本月資料篩選

SELECT SUM(total), CONVERT(CHAR(10), createtime, 120) AS day
FROM orders
WHERE DATEPART(month, createtime) = DATEPART(month, GETDATE())
  AND DATEPART(year, createtime) = DATEPART(year, GETDATE())
GROUP BY CONVERT(CHAR(10), createtime, 120);

MySQL 寫法

按日統計

SELECT SUM(total) AS daily_total,
       DATE(createtime) AS day
FROM orders
GROUP BY DATE(createtime)
ORDER BY day;

按月統計

SELECT SUM(total) AS monthly_total,
       DATE_FORMAT(createtime, '%Y-%m') AS month
FROM orders
GROUP BY DATE_FORMAT(createtime, '%Y-%m')
ORDER BY month;

本月篩選

SELECT SUM(total), DATE(createtime) AS day
FROM orders
WHERE createtime >= DATE_FORMAT(NOW(), '%Y-%m-01')
  AND createtime < DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH), '%Y-%m-01')
GROUP BY DATE(createtime);

通用技巧：利用 FLOOR/CAST 去除時間部分

-- MSSQL
SELECT CAST(FLOOR(CAST(createtime AS FLOAT)) AS DATETIME) AS day, SUM(total)
FROM orders GROUP BY FLOOR(CAST(createtime AS FLOAT));

-- MySQL
SELECT DATE(createtime) AS day, SUM(total)
FROM orders GROUP BY DATE(createtime);

效能建議

• 對 createtime 建立索引（單欄或複合）。
• 大表分區按月存放，配合分區裁剪。
• 避免在 GROUP BY 欄位上使用函數（MySQL 8.0+ 支援函數索引可緩解）。