根据唯一 ID - Oracle 更新所有行的值

Question

我有一个表格：

表1

unique_id       col_id      col_nm      col_val     sequ    s_date      e_date
1               1           test 1      100         1       null        null
1               2           test 2      abc         1       null        null
1               3           test 3      103         1       null        null
1               4           test 4      105         2       null        null
1               5           test 5      10          1       null        null
1               6           my date     20180912    1       null        null
1               7           my date 1   20180913    2       null        null
1               8           test 6      100         1       null        null

2               1           test 1      100         1       null        null
2               2           test 2      abc         1       null        null
2               3           test 3      103         1       null        null
2               4           test 4      105         2       null        null
2               5           test 5      10          1       null        null
2               6           my date     20180911    1       null        null
2               7           my date 1   20180910    2       null        null
2               8           test 6      100         1       null        null

对于每个unique_id，我需要获取col_val并设置s_date并e_date：

• s_date = col_val col_id = 6 和 col_nm = "我的日期"和序列 = 1
• e_date = col_val 的 col_id = 7 和 col_nm = '我的约会 1' 和序列 = 2

，然后为相应unique_id的所有行设置它。

输出表如下所示：

unique_id       col_id      col_nm      col_val     sequ    s_date      e_date
1               1           test 1      100         1       20180912    20180913
1               2           test 2      abc         1       20180912    20180913
1               3           test 3      103         1       20180912    20180913
1               4           test 4      105         2       20180912    20180913
1               5           test 5      10          1       20180912    20180913
1               6           my date     20180912    1       20180912    20180913
1               7           my date 1   20180913    2       20180912    20180913
1               8           test 6      100         1       20180912    20180913

2               1           test 1      100         1       20180911    20180910
2               2           test 2      abc         1       20180911    20180910
2               3           test 3      103         1       20180911    20180910
2               4           test 4      105         2       20180911    20180910
2               5           test 5      10          1       20180911    20180910
2               6           my date     20180911    1       20180911    20180910
2               7           my date 1   20180910    2       20180911    20180910
2               8           test 6      100         1       20180911    20180910

我的表有超过 5000 万条记录，因此需要查询来考虑这一点。

SQL中有没有办法实现这一点？

Answer 1

您可以尝试使用具有条件加重功能的MERGE

MERGE
INTO T trg
USING   (
         SELECT unique_id,
           MAX(CASE WHEN col_id = 6 AND col_nm = 'my date' and sequ = 1 THEN col_val END) mindt,
           MIN(CASE WHEN col_id = 7 AND col_nm = 'my date 1' and sequ = 2 THEN col_val END) maxdt
         FROM T
         GROUP BY unique_id
        ) src
ON  (trg.unique_id = src.unique_id)
WHEN MATCHED THEN UPDATE
    SET trg.e_date = maxdt , trg.s_date = mindt

结果：

| UNIQUE_ID | COL_ID |    COL_NM |  COL_VAL | SEQU |   S_DATE |   E_DATE |
|-----------|--------|-----------|----------|------|----------|----------|
|         1 |      1 |   test 1  |      100 |    1 | 20180912 | 20180913 |
|         1 |      2 |   test 2  |      abc |    1 | 20180912 | 20180913 |
|         1 |      3 |   test 3  |      103 |    1 | 20180912 | 20180913 |
|         1 |      4 |   test 4  |      105 |    2 | 20180912 | 20180913 |
|         1 |      5 |   test 5  |       10 |    1 | 20180912 | 20180913 |
|         1 |      6 |   my date | 20180912 |    1 | 20180912 | 20180913 |
|         1 |      7 | my date 1 | 20180913 |    2 | 20180912 | 20180913 |
|         1 |      8 |   test 6  |      100 |    1 | 20180912 | 20180913 |
|         2 |      1 |   test 1  |      100 |    1 | 20180911 | 20180910 |
|         2 |      2 |   test 2  |      abc |    1 | 20180911 | 20180910 |
|         2 |      3 |   test 3  |      103 |    1 | 20180911 | 20180910 |
|         2 |      4 |   test 4  |      105 |    2 | 20180911 | 20180910 |
|         2 |      5 |   test 5  |       10 |    1 | 20180911 | 20180910 |
|         2 |      6 |   my date | 20180911 |    1 | 20180911 | 20180910 |
|         2 |      7 | my date 1 | 20180910 |    2 | 20180911 | 20180910 |
|         2 |      8 |   test 6  |      100 |    1 | 20180911 | 20180910 |

Answer 2

您可以尝试以下查询：

MERGE INTO TABLE1 T1 USING (
                               SELECT
                                   START_DATE.UNIQUE_ID,
                                   START_DATE.COL_VAL   AS START_DATE,
                                   END_DATE.COL_VAL     AS END_DATE
                               FROM
                                   (
                                       SELECT
                                           T.UNIQUE_ID,
                                           T.COL_VAL
                                       FROM
                                           TABLE1 T
                                       WHERE
                                           T.COL_ID = 6
                                           AND T.COL_NM = 'my date'
                                   ) START_DATE,
                                   (
                                       SELECT
                                           T.UNIQUE_ID,
                                           T.COL_VAL
                                       FROM
                                           TABLE1 T
                                       WHERE
                                           T.COL_ID = 7
                                           AND T.COL_NM = 'my date 1'
                                   ) END_DATE
                               WHERE
                                   START_DATE.UNIQUE_ID = END_DATE.UNIQUE_ID
                           )
DATES ON ( T1.UNIQUE_ID = DATES.UNIQUE_ID ) WHEN MATCHED THEN
UPDATE SET
    T1.S_DATE = DATES.START_DATE,
    T1.E_DATE = DATES.END_DATE;

演示

谢谢。