如何按销售人员按日统计销售数量？SQL查询优化求助

直接调整查询逻辑，移除导致分组粒度太细的entdatas.id，同时优化销售人员信息的获取方式，修改后的SQL如下：

SELECT
    CONCAT(u.firstname, ' ', u.lastname) AS salesman,
    COUNT(DISTINCT e.id) AS no_of_orders,
    CAST(e.created_at AS DATE) AS order_date
FROM
    entdatas e
JOIN
    entdatadetails ed ON e.id = ed.entdata_id
JOIN
    entfields ef ON ed.entfield_id = ef.id
JOIN
    users u ON ed.value = u.id
WHERE
    ef.fld_code = 'order_salesman'
    AND e.isActive = 1
    AND CAST(e.created_at AS DATE) < CAST(GETDATE() AS DATE)
GROUP BY
    CONCAT(u.firstname, ' ', u.lastname), CAST(e.created_at AS DATE)
ORDER BY
    order_date ASC

关键调整说明： #

• 用关联查询替代嵌套子查询获取销售人员姓名，逻辑更清晰且性能更优
• 彻底移除SELECT和GROUP BY中的entdatas.id，确保仅按销售人员和日期分组
• 使用COUNT(DISTINCT e.id)避免因entdatadetails多关联记录导致的订单重复统计
• 给日期字段命名为order_date，提升结果可读性

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