假设我理解了您的问题，即您正在工作流中的特定步骤查找业务对象，那么您可以尝试以下方法：

DECLARE @step table ( id_step int, code varchar(10), [level] int );
INSERT INTO @step ( id_step, code, [level] ) VALUES
    ( 1, 'STEP_1', 10 ),
    ( 2, 'STEP_2', 20 ),
    ( 3, 'STEP_3', 30 ),
    ( 4, 'STEP_4', 40 ),
    ( 5, 'STEP_5', 50 );

DECLARE @workflow table ( id_workflow int, id_step int, id_object varchar(50) );
INSERT INTO @workflow ( id_workflow, id_step, id_object ) VALUES
    ( 100, 1, 'BUSINESS_OBJECT_1' ),
    ( 101, 2, 'BUSINESS_OBJECT_1' ),
    ( 102, 3, 'BUSINESS_OBJECT_1' ),
    ( 103, 4, 'BUSINESS_OBJECT_1' ),
    ( 104, 5, 'BUSINESS_OBJECT_1' ),
    ( 105, 1, 'BUSINESS_OBJECT_2' ),
    ( 106, 2, 'BUSINESS_OBJECT_2' ),
    ( 107, 3, 'BUSINESS_OBJECT_2' );

-- workflow id_step to query.
DECLARE @workflow_id_step int = 3;

-- return workflows currently at id_step 3.
SELECT
    *
FROM @workflow AS workflow
INNER JOIN @step AS step
    ON workflow.id_step = step.id_step
WHERE
    -- queried step
    workflow.id_step = @workflow_id_step
    -- exclude ID_OBJECT workflows that are further along than the queried @workflow_id_step
    AND NOT EXISTS (
        SELECT * FROM @workflow AS w WHERE 
            w.id_object = workflow.id_object    -- important to join in the id_object value
            AND w.id_step > workflow.id_step    -- look for steps greater than the queried id_step
    )
ORDER BY
    workflow.id_object, workflow.id_workflow;

回报

+-------------+---------+-------------------+---------+--------+-------+
| id_workflow | id_step |     id_object     | id_step |  code  | level |
+-------------+---------+-------------------+---------+--------+-------+
|         107 |       3 | BUSINESS_OBJECT_2 |       3 | STEP_3 |    30 |
+-------------+---------+-------------------+---------+--------+-------+

注

• 我添加了一些注解以帮助理解逻辑。
• 假设步骤CODE值与其ID_STEP值同步（例如，id_step：1 =代码：步骤1等）。
• 假设各个工作流按id_object分组。
  更新

要改为基于步骤级别进行筛选...

-- return workflows currently at id_step 3 that do not have a greater step-level.
SELECT
    *
FROM @workflow AS workflow
INNER JOIN @step AS step
    ON workflow.id_step = step.id_step
WHERE
    -- queried step
    workflow.id_step = @workflow_id_step
    -- exclude ID_OBJECT workflows that are further along than the queried @workflow_id_step
    AND NOT EXISTS (
        SELECT * FROM @workflow AS w 
        INNER JOIN @step AS s
            ON w.id_step = s.id_step
        WHERE 
            w.id_object = workflow.id_object    -- important to join in the id_object value
            AND s.[level] > step.[level]        -- look for steps with a greater level than the queried id_step level
    )
ORDER BY
    workflow.id_object, workflow.id_workflow;

回报

+-------------+---------+-------------------+---------+--------+-------+
| id_workflow | id_step |     id_object     | id_step |  code  | level |
+-------------+---------+-------------------+---------+--------+-------+
|         107 |       3 | BUSINESS_OBJECT_2 |       3 | STEP_3 |    30 |
+-------------+---------+-------------------+---------+--------+-------+