如何创建SQL存储过程输出接诊量≥15医生的逗号分隔姓名列表

查询接诊病例数≥15的医生，涉及两张表如下：

• 表1：
• 表2：

select 
    d.DoctorName 
from
    tblDoctor d, tblEpisode e 
where 
    e.DoctorId = d.DoctorId
group by 
    d.DoctorId, d.doctorname
having 
    count(d.DoctorID) > 15;

现有查询输出：

创建一个存储过程，将接诊量≥15的医生姓名拼接为逗号分隔的字符串作为输出变量返回。

以下给出主流数据库的实现代码：

MySQL 版本 #

-- 修改分隔符避免和存储过程内的分号冲突
DELIMITER //
CREATE PROCEDURE GetHighVolumeDoctors(OUT doctor_names_str VARCHAR(4000))
BEGIN
    SELECT GROUP_CONCAT(DISTINCT d.DoctorName SEPARATOR ',') INTO doctor_names_str
    FROM tblDoctor d
    INNER JOIN tblEpisode e ON e.DoctorId = d.DoctorId
    GROUP BY d.DoctorId, d.DoctorName
    -- 修正条件为≥15，匹配需求
    HAVING COUNT(e.EpisodeId) >= 15;
END //
DELIMITER ;

调用方式：

CALL GetHighVolumeDoctors(@result);
SELECT @result;

SQL Server 2017+ 版本 #

CREATE PROCEDURE GetHighVolumeDoctors
    @doctor_names_str NVARCHAR(MAX) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @doctor_names_str = STRING_AGG(DoctorName, ',')
    FROM (
        SELECT d.DoctorName
        FROM tblDoctor d
        INNER JOIN tblEpisode e ON e.DoctorId = d.DoctorId
        GROUP BY d.DoctorId, d.DoctorName
        HAVING COUNT(e.EpisodeId) >= 15
    ) AS qualified_doctors;
END

调用方式：

DECLARE @res NVARCHAR(MAX);
EXEC GetHighVolumeDoctors @doctor_names_str = @res OUTPUT;
SELECT @res;

Oracle 版本 #

CREATE OR REPLACE PROCEDURE GetHighVolumeDoctors(doctor_names_str OUT VARCHAR2)
IS
BEGIN
    SELECT LISTAGG(DoctorName, ',') WITHIN GROUP (ORDER BY DoctorName) INTO doctor_names_str
    FROM (
        SELECT d.DoctorName
        FROM tblDoctor d
        INNER JOIN tblEpisode e ON e.DoctorId = d.DoctorId
        GROUP BY d.DoctorId, d.DoctorName
        HAVING COUNT(e.EpisodeId) >= 15
    );
END;
/

• 原查询中的count(d.DoctorID) > 15已修正为COUNT(e.EpisodeId) >= 15，完全匹配需求要求的接诊数≥15的条件
• 采用显式INNER JOIN代替隐式逗号表连接，SQL可读性更强
• 若医生数量较多，需注意调整拼接函数的长度限制：例如MySQL默认group_concat_max_len为1024字符，可根据需求提前调整参数值

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