如何将MariaDB存储过程JOIN查询结果的user_id赋值给OUT参数

要把查询结果中的user_id赋值给OUT参数xnode，可以通过以下两种方式修改存储过程：

方法一：使用SELECT ... INTO直接赋值 #

直接在查询语句中把user_id的值写入xnode，这是MariaDB中给变量赋值最直接的方式：

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_sp`(IN `xid` INT, IN `xpool_id` INT, IN `xtree` INT, OUT `xnode` INT)
BEGIN
      WITH RECURSIVE generation AS 
      ( SELECT parent_id, user_id FROM user_pool WHERE user_id=xid AND pool_id=xpool_id
      UNION ALL 
      SELECT 
      child.parent_id, 
      child.user_id
      FROM user_pool child 
      JOIN generation g ON g.user_id = child.parent_id WHERE child.pool_id=xpool_id) 
      
    -- 直接将查询出的user_id赋值给xnode
    SELECT g1.user_id INTO xnode
    FROM generation g1 LEFT JOIN
      (SELECT parent_id,COUNT(*) CountOfChild FROM generation GROUP BY parent_id) g2
      ON g1.user_id=g2.parent_id 
    HAVING CountOfChild < xtree 
    ORDER BY user_id, CountOfChild 
    LIMIT 1;
 
  END$$
DELIMITER ;

方法二：使用SET结合子查询赋值 #

如果需要保留原查询的结果集输出，同时给变量赋值，可以用SET语句结合子查询：

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_sp`(IN `xid` INT, IN `xpool_id` INT, IN `xtree` INT, OUT `xnode` INT)
BEGIN
      WITH RECURSIVE generation AS 
      ( SELECT parent_id, user_id FROM user_pool WHERE user_id=xid AND pool_id=xpool_id
      UNION ALL 
      SELECT 
      child.parent_id, 
      child.user_id
      FROM user_pool child 
      JOIN generation g ON g.user_id = child.parent_id WHERE child.pool_id=xpool_id) 
      
    -- 先执行查询返回结果集
    SELECT g1.user_id, g1.parent_id, COALESCE(g2.CountOfChild,0) as CountOfChild
    FROM generation g1 LEFT JOIN
      (SELECT parent_id,COUNT(*) CountOfChild FROM generation GROUP BY parent_id) g2
      ON g1.user_id=g2.parent_id 
    HAVING CountOfChild < xtree 
    ORDER BY user_id, CountOfChild 
    LIMIT 1;

    -- 再通过子查询赋值给xnode
    SET xnode = (
      SELECT g1.user_id
      FROM generation g1 LEFT JOIN
        (SELECT parent_id,COUNT(*) CountOfChild FROM generation GROUP BY parent_id) g2
        ON g1.user_id=g2.parent_id 
      HAVING CountOfChild < xtree 
      ORDER BY user_id, CountOfChild 
      LIMIT 1
    );
 
  END$$
DELIMITER ;

注意事项 #

• 你的查询通过LIMIT 1确保只返回单行结果，因此不会出现多值赋值的报错。
• 如果查询可能返回空结果，建议提前给xnode设置默认值（比如SET xnode = NULL;），避免变量未初始化。

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