SQL Server 用于检查给定步骤是否是工作流中的最后一个高级步骤的Sql查询

rmbxnbpk  于 2022-12-17  发布在  其他
关注(0)|答案(1)|浏览(112)

我有以下两张表:

|_________________WORKFLOW__________________||___________STEP___________|
| ID_WORKFLOW | ID_STEP |     ID_OBJECT     || ID_STEP |  CODE  | LEVEL | 
|-------------|---------|-------------------||---------|--------|-------|
|     100     |    1    | BUSNIESS_OBJECT_1 ||    1    | STEP_1 |   10  |
|     101     |    2    | BUSNIESS_OBJECT_1 ||    2    | STEP_2 |   20  |
|     102     |    3    | BUSNIESS_OBJECT_1 ||    3    | STEP_3 |   30  |
|     103     |    4    | BUSNIESS_OBJECT_1 ||    4    | STEP_4 |   40  |
|     104     |    5    | BUSNIESS_OBJECT_1 ||    5    | STEP_5 |   50  |
|     105     |    1    | BUSNIESS_OBJECT_2 |----------------------------
|     106     |    2    | BUSNIESS_OBJECT_2 |
|     107     |    3    | BUSNIESS_OBJECT_2 |
---------------------------------------------

我想查询WORKFLOW表格,过滤STEP-〉CODE列,并具有最后一个STEP-〉LEVEL。
例如,我想从STEP-〉CODE = 'STEP_3'的工作流表格中获取工作流。在结果中,我期望ID_WORKFLOW = 107。但不是ID_WORKFLOW = 102。因为这个工作流具有高级步骤4(ID_WORKFLOW = 103),所以STEP-〉LEVEL大于40〉30。
我不知道是否可以使用简单的sql查询和一些连接来得到结果,或者我是否需要为此使用游标。
非常感谢您事先的建议,

s5a0g9ez

s5a0g9ez1#

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

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 |
+-------------+---------+-------------------+---------+--------+-------+

相关问题