如何使用连接编写存储过程？并在指定SQL中查询Role表name列

要在现有查询中取出Role表的name列，需修改内层关联employee的子查询，直接关联Role表获取name字段，修改后的完整SQL如下：

SELECT 
    ud.id AS userId, ud.organization_id,
    ud.first_name, ud.last_name,
    ud.image_url, ud.role_name, -- 新增角色名称字段
    agentCount, dealerCount, IFNULL(inventoryCount, 0) inventoryCount
 
FROM
    (SELECT ju.*, userOrg.organization_id, userOrg.role_name -- 包含新增的role_name字段
     FROM jhi_user ju 
     INNER JOIN 
         (SELECT e.user_id, e.organization_id, r.name AS role_name -- 关联Role表获取name
          FROM employee e 
          INNER JOIN role r ON e.role_id = r.id -- 新增与Role表的连接
          WHERE r.organization_id IN (SELECT id 
                                      FROM organization o 
                                      WHERE sales_person_id = 1) 
            AND r.name = 'NEXCAR_SALES_PERSON' 
            AND (e.deleted = 0 OR e.deleted IS NULL)) userOrg ON ju.id = userOrg.user_id
    ) ud
LEFT JOIN 
    (SELECT sales_person_id, id, count(id) as dealerCount 
     FROM organization o  
     GROUP BY sales_person_id) odetails ON odetails.sales_person_id  = ud.id
LEFT JOIN 
    (SELECT o.id, COUNT(o.id) AS agentCount 
     FROM employee e
     LEFT JOIN organization o ON e.organization_id = o.id 
     WHERE e.user_id = 1 
     GROUP BY o.id) edetails ON ud.organization_id = edetails.id
LEFT JOIN 
    (SELECT i.organization_id, COUNT(id) AS inventoryCount 
     FROM inventory i) idetails ON idetails.organization_id = odetails.id

核心修改点：

• 在userOrg子查询中，通过INNER JOIN role r ON e.role_id = r.id关联Role表，取出r.name并命名为role_name
• 外层ud子查询中明确指定字段（替代原SELECT *），包含新增的userOrg.role_name
• 最终SELECT列表加入ud.role_name，即可返回角色名称

可以把上述查询封装成带参数的存储过程，提升复用性，示例如下：

DELIMITER //
CREATE PROCEDURE GetSalesPersonDetails(IN p_sales_person_id INT)
BEGIN
    SELECT 
        ud.id AS userId, ud.organization_id,
        ud.first_name, ud.last_name,
        ud.image_url, ud.role_name,
        agentCount, dealerCount, IFNULL(inventoryCount, 0) inventoryCount
    FROM
        (SELECT ju.*, userOrg.organization_id, userOrg.role_name
         FROM jhi_user ju 
         INNER JOIN 
             (SELECT e.user_id, e.organization_id, r.name AS role_name
              FROM employee e 
              INNER JOIN role r ON e.role_id = r.id
              WHERE r.organization_id IN (SELECT id 
                                          FROM organization o 
                                          WHERE sales_person_id = p_sales_person_id) 
                AND r.name = 'NEXCAR_SALES_PERSON' 
                AND (e.deleted = 0 OR e.deleted IS NULL)) userOrg ON ju.id = userOrg.user_id
        ) ud
    LEFT JOIN 
        (SELECT sales_person_id, id, count(id) as dealerCount 
         FROM organization o  
         GROUP BY sales_person_id) odetails ON odetails.sales_person_id  = ud.id
    LEFT JOIN 
        (SELECT o.id, COUNT(o.id) AS agentCount 
         FROM employee e
         LEFT JOIN organization o ON e.organization_id = o.id 
         WHERE e.user_id = p_sales_person_id 
         GROUP BY o.id) edetails ON ud.organization_id = edetails.id
    LEFT JOIN 
        (SELECT i.organization_id, COUNT(id) AS inventoryCount 
         FROM inventory i) idetails ON idetails.organization_id = odetails.id;
END //
DELIMITER ;

调用存储过程：

CALL GetSalesPersonDetails(1); -- 传入目标sales_person_id参数

存储过程编写要点：

• 用DELIMITER修改语句结束符，避免与存储过程内部分号冲突
• 定义输入参数p_sales_person_id，替换原查询中固定的sales_person_id = 1，让逻辑更通用

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