如何编写SQL按天统计每位销售人员的销售金额总和

基础实现逻辑 #

核心思路是按「销售人员+销售日期」两个维度分组，对单笔销售金额求和即可。
默认你的销售表表名为sales，表结构包含以下核心字段：

• sales_name：销售人员姓名，存储Frank这类人员名称
• deal_date：销售成交日期
• deal_amount：单笔订单销售金额

基础统计SQL如下：

SELECT
  sales_name,
  deal_date,
  SUM(deal_amount) AS total_sale_amount
FROM sales
GROUP BY sales_name, deal_date
ORDER BY sales_name, deal_date;

常见场景适配 #

1. 日期字段带时分秒的处理 #

如果deal_date是datetime类型带具体时分秒，直接分组会把同一天不同时间的订单拆成不同组，需要先把字段转为纯日期格式再分组，不同数据库写法略有区别：

• MySQL写法：

SELECT
  sales_name,
  DATE(deal_date) AS deal_date,
  SUM(deal_amount) AS total_sale_amount
FROM sales
GROUP BY sales_name, DATE(deal_date)
ORDER BY sales_name, DATE(deal_date);

• PostgreSQL写法：

SELECT
  sales_name,
  deal_date::DATE AS deal_date,
  SUM(deal_amount) AS total_sale_amount
FROM sales
GROUP BY sales_name, deal_date::DATE
ORDER BY sales_name, deal_date::DATE;

• SQL Server写法：

SELECT
  sales_name,
  CAST(deal_date AS DATE) AS deal_date,
  SUM(deal_amount) AS total_sale_amount
FROM sales
GROUP BY sales_name, CAST(deal_date AS DATE)
ORDER BY sales_name, CAST(deal_date AS DATE);

2. 同时显示周几（周一/周二）信息 #

如果需要和示例要求一样返回日期对应的星期信息，加对应数据库的星期取值函数即可，以MySQL为例查询Frank的个人销售汇总：

SELECT
  DATE(deal_date) AS deal_date,
  DAYNAME(deal_date) AS week_day,
  SUM(deal_amount) AS total_sale_amount
FROM sales
WHERE sales_name = 'Frank'
GROUP BY DATE(deal_date), DAYNAME(deal_date)
ORDER BY deal_date;

3. 按星期维度统计（不区分具体日期） #

如果不需要具体自然日，只需要统计每个销售周一到周日分别的总销售额，直接按星期字段分组即可：

-- MySQL示例
SELECT
  sales_name,
  DAYNAME(deal_date) AS week_day,
  SUM(deal_amount) AS total_sale_amount
FROM sales
GROUP BY sales_name, DAYNAME(deal_date)
-- 按周一到周日排序可以加ORDER BY WEEKDAY(deal_date)
ORDER BY sales_name, WEEKDAY(deal_date);

内容的提问来源于stack exchange，提问作者xafiy