审查视图

sql/2023-05-04_2.sql 1.4 KB
tangxvhui authored
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77
-- 更新staff_assess
WITH t_staff_assess AS (
    SELECT
        *
    FROM
        staff_assess
    WHERE
        1 = 1
        AND cycle_id = 1653724699349225472
        AND NOT EXISTS (
            SELECT
                *
            FROM
                staff_assess_content
            WHERE
                staff_assess_content.staff_assess_id = staff_assess.id))
UPDATE
    staff_assess
SET
    deleted_at = now()
WHERE
    1 = 1
    AND staff_assess.id IN (
        SELECT
            id
        FROM
            t_staff_assess);

-- 更新staff_assess
WITH t_staff_assess AS (
    SELECT
        row_number() OVER (PARTITION BY evaluation_project_id,
            "types",
            staff_assess.begin_time) AS row_id,
        *
    FROM
        staff_assess
    WHERE
        1 = 1
        AND cycle_id = 1653724699349225472
        AND NOT EXISTS (
            SELECT
                *
            FROM
                staff_assess_content
            WHERE
                staff_assess_content.staff_assess_id = staff_assess.id)
),
t_staff_assess_2 AS (
    SELECT
        *
    FROM
        t_staff_assess
    WHERE
        row_id = 1)
UPDATE
    staff_assess
SET
    deleted_at = NULL
WHERE
    1 = 1
    AND cycle_id = 1653724699349225472
    AND staff_assess.id IN (
        SELECT
            id
        FROM
            t_staff_assess_2);

;

UPDATE
    staff_assess
SET
    deleted_at = now()
WHERE
    id IN (18404, 18458);