MySQL中如何用officetree存储过程结果作为IN子查询条件？求语法

Absolutely! You can definitely pull this off. The catch is that MySQL doesn’t let you directly nest a stored procedure call inside an IN clause—you need to first capture the stored procedure’s output, then use that to filter your employee data. Here are two solid approaches:

Approach 1: Use a Temporary Table (No Changes to Your Existing Stored Procedure) #

Since you already have officetree(15) set up, you can dump its results into a temporary table, then reference that table in your employee query. Here’s how:

-- Step 1: Create a temporary table to hold the office IDs
CREATE TEMPORARY TABLE temp_office_ids (
    ofc_id INT
);

-- Step 2: Populate the temp table with your stored procedure's output
INSERT INTO temp_office_ids
CALL officetree(15);

-- Step 3: Query employees using the temp table's IDs
SELECT * 
FROM master_employee
WHERE officeid IN (SELECT ofc_id FROM temp_office_ids);

-- Optional: Clean up the temp table (it auto-drops when your session ends)
DROP TEMPORARY TABLE temp_office_ids;

This works because temporary tables are session-specific—they won’t interfere with other users, and they’re automatically deleted when you close your database connection.

Approach 2: Convert the Logic to a Table-Valued Function (More Elegant for Reuse) #

If you’re open to modifying your original logic, a table-valued function is a cleaner solution. Unlike stored procedures, functions can return a result set that you can directly use in IN clauses or joins. Here’s how to rewrite your stored procedure’s recursive tree logic as a function:

DELIMITER //
CREATE FUNCTION get_office_tree(ofcid INT)
RETURNS TABLE
BEGIN
    RETURN (
        SELECT `ofc_id` 
        FROM (SELECT * FROM master_office ORDER BY `ofc_parent_id`, `ofc_id`) master_office, 
             (SELECT @pv := ofcid) office 
        WHERE (FIND_IN_SET(`ofc_parent_id`, @pv) > 0 AND @pv := CONCAT(@pv, ',', `ofc_id`)) 
           OR ofc_id = ofcid
    );
END //
DELIMITER ;

Once the function is created, you can query your employees in one step—either with an IN clause:

SELECT * 
FROM master_employee
WHERE officeid IN (SELECT ofc_id FROM get_office_tree(15));

Or for better performance (especially with large datasets), use a JOIN:

SELECT me.* 
FROM master_employee me
JOIN get_office_tree(15) ot ON me.officeid = ot.ofc_id;

Why This Works #

Your original stored procedure uses a recursive variable trick (@pv) to traverse the office hierarchy. Both approaches preserve that logic—they just package it in a way that lets you use the output directly in your employee query.

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