SQL Server Sql query with an unusel restrictions

pbgvytdp  于 2022-12-10  发布在  其他
关注(0)|答案(1)|浏览(130)

I've the 2 following tables:

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

I want to query WORKFLOW table filtering on STEP->CODE column and having the last STEP->LEVEL.
For example, I'd like to get from workflow table workflows where STEP->CODE = 'STEP_3'. In the result, I expect ID_WORKFLOW = 107. But not ID_WORKFLOW = 102. Because this one has an advanced step of 4 (ID_WORKFLOW = 103) It's STEP->LEVEL is greater than 40>30.
I don't know if it's possible to get the result using a simple sql query with some joins or if I need to definitely use a cursor for this.
Many thanks for your advices in advance,

z9ju0rcb

z9ju0rcb1#

Assuming I understand your question, that you are looking for business objects at a particular step in the workflow, here is something you can try:

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;

RETURNS

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

NOTES

  • I added a few comments to help understand the logic.
  • Assumed your step CODE values are in sync with their ID_STEP values (e.g., id_step: 1 = code: Step 1, etc.).
  • Assumed individual workflows are grouped by id_object .
    UPDATE

To filter based on a steps level instead...

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

RETURNS

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

相关问题