Mysql query for payment：按医生分摊技术人员日薪

结论

这项计算可以全部用 SQL 完成，无需在 PHP 中逐日遍历。

先按“日期 + 技术人员”分组，用 COUNT(DISTINCT ref_doctor_id) 统计技术人员当天服务过的医生人数，再筛选出包含指定医生的分组。指定医生当天分摊的成本为：

技术人员日薪 / 当天服务的不同医生数量

MySQL 8.0 及以上

下面的查询会同时返回每日分摊金额和整个查询期间的总金额：

WITH daily_cost AS (
    SELECT
        DATE(dos) AS work_date,
        technician_id,
        COUNT(DISTINCT ref_doctor_id) AS doctor_count,
        100.00 / COUNT(DISTINCT ref_doctor_id) AS allocated_cost
    FROM logs
    WHERE technician_id = 16
      AND dos >= '2013-03-05'
      AND dos <  '2013-03-08'
    GROUP BY DATE(dos), technician_id
    HAVING COUNT(DISTINCT CASE
               WHEN ref_doctor_id = 6 THEN ref_doctor_id
           END) > 0
)
SELECT
    work_date,
    technician_id,
    doctor_count,
    ROUND(allocated_cost, 2) AS daily_cost,
    ROUND(SUM(allocated_cost) OVER (), 2) AS period_total
FROM daily_cost
ORDER BY work_date;

查询结果如下：

医生条件不能直接写在 WHERE 中：

ref_doctor_id = 6

这样会在分组前排除其他医生的记录，导致 COUNT(DISTINCT ref_doctor_id) 始终为 1。应当把医生条件放在 HAVING 中，先统计当天的所有医生，再判断分组中是否包含 doctor 6。

只查询期间总成本

如果只需要 doctor 6 在该期间分摊的总金额，可以使用下面的查询：

SELECT ROUND(SUM(100.00 / doctor_count), 2) AS total_cost
FROM (
    SELECT
        DATE(dos) AS work_date,
        technician_id,
        COUNT(DISTINCT ref_doctor_id) AS doctor_count
    FROM logs
    WHERE technician_id = 16
      AND dos >= '2013-03-05'
      AND dos <  '2013-03-08'
    GROUP BY DATE(dos), technician_id
    HAVING COUNT(DISTINCT CASE
               WHEN ref_doctor_id = 6 THEN ref_doctor_id
           END) > 0
) AS daily;

结果为：

183.33

使用参数

实际应用中应使用预处理参数，不要把医生 ID、技术人员 ID 和日薪直接拼接到 SQL 中：

WITH daily_cost AS (
    SELECT
        DATE(dos) AS work_date,
        COUNT(DISTINCT ref_doctor_id) AS doctor_count
    FROM logs
    WHERE technician_id = :technician_id
      AND dos >= :date_from
      AND dos <  :date_to_exclusive
    GROUP BY DATE(dos), technician_id
    HAVING COUNT(DISTINCT CASE
               WHEN ref_doctor_id = :doctor_id THEN ref_doctor_id
           END) > 0
)
SELECT
    work_date,
    doctor_count,
    ROUND(:daily_rate / doctor_count, 2) AS daily_cost,
    ROUND(SUM(:daily_rate / doctor_count) OVER (), 2) AS period_total
FROM daily_cost
ORDER BY work_date;

参数含义如下：

• :doctor_id 是指定医生，例如 6
• :technician_id 是指定技术人员，例如 16
• :daily_rate 是日薪，例如 100.00
• :date_to_exclusive 是查询范围不包含的结束日期

注意事项

• COUNT(DISTINCT ref_doctor_id) 可以避免同一技术人员在同一天为同一医生完成多个 job 时重复计数。
• 如果 dos 是 DATE 类型，可以直接按 dos 分组，无需使用 DATE(dos)。
• 如果 dos 是 DATETIME 或 TIMESTAMP，采用 dos >= 开始时间 AND dos < 次日时间 这样的半开日期范围，通常比 DATE(dos) = ... 更容易利用索引。
• 财务数据建议使用 DECIMAL，不要用 FLOAT 或 DOUBLE 保存金额。
• 示例查询先按完整精度累计，最后对总额四舍五入。如果业务规则要求每天先结算到分，再计算总额，应改为：

SUM(ROUND(:daily_rate / doctor_count, 2))

两种计算方式在某些数据下可能相差几分钱，应根据实际财务口径选择。

备注：内容仅供参考。