查询上月接诊患者最多的医生的SQL语句请求咨询

阿华AIGC实验室

2026-5-13

查询上月接诊患者数量最多的医生的SQL方案

首先得明确几个关键点：我们要统计的是上月的接诊记录，并且是「接诊患者数量」（也就是不同患者的数量，而非就诊次数——如果是后者的话调整下计数方式就行）。下面分不同数据库给出可行的SQL语句，同时拆解每一步的逻辑：

核心思路拆解

先从visits表中筛选出上月的就诊记录
关联doctors表获取医生的详细信息
统计每个医生上月接诊的不同患者数量
找出接诊数最多的医生（支持多个医生并列第一的情况）

MySQL 版本

-- 先统计每个医生上月的接诊患者数，再找出最大值对应的医生
WITH doctor_visit_counts AS (
    SELECT 
        d.id AS doctor_id,
        d.name AS doctor_name,
        d.profession,
        COUNT(DISTINCT v.patientId) AS patient_count
    FROM doctors d
    JOIN visits v ON d.id = v.doctorId
    -- 用年月匹配筛选上月记录，避免跨月边界问题（比如2月最后一天）
    WHERE DATE_FORMAT(v.visitDate, '%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m')
    GROUP BY d.id, d.name, d.profession
),
max_count AS (
    SELECT MAX(patient_count) AS max_patients FROM doctor_visit_counts
)
SELECT 
    doctor_id,
    doctor_name,
    profession,
    patient_count
FROM doctor_visit_counts
JOIN max_count ON doctor_visit_counts.patient_count = max_count.max_patients;

PostgreSQL 版本

WITH doctor_visit_counts AS (
    SELECT 
        d.id AS doctor_id,
        d.name AS doctor_name,
        d.profession,
        COUNT(DISTINCT v.patientId) AS patient_count
    FROM doctors d
    JOIN visits v ON d.id = v.doctorId
    -- 筛选上月的就诊记录
    WHERE EXTRACT(YEAR FROM v.visitDate) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '1 month')
      AND EXTRACT(MONTH FROM v.visitDate) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL '1 month')
    GROUP BY d.id, d.name, d.profession
),
max_count AS (
    SELECT MAX(patient_count) AS max_patients FROM doctor_visit_counts
)
SELECT 
    doctor_id,
    doctor_name,
    profession,
    patient_count
FROM doctor_visit_counts
JOIN max_count ON doctor_visit_counts.patient_count = max_count.max_patients;