根据每个唯一标识符的条件更新值

Question

我有一个查询，我想根据每个主键的特定条件更新一个值。例如，如果TYPE是"石棉"，而这个特定项目不存在TYPE"CONTIN"，我就喜欢将"石棉"更改为"CONTIN"。

我已经通过下面的查询实现了这一目标，但这会将整个人口石棉值更新为 CONTIN，即使 CONTIN 已经存在，基本上也会导致重复值。仅当 CONTIN 尚不存在时才应添加它。也许一个 windows 函数可以工作，以便它查看每个单独的项目，但我得到一个语法错误。感谢您的帮助！

SELECT PROJECT, 
CASE WHEN TYPE = 'ASBESTOS' AND TYPE NOT IN 'CONTIN' THEN 'CONTIN' ELSE TYPE END AS TYPE, 
SUM(OBLIGATION) AS OBS 
FROM OBS_MASTER
WHERE PROJECT = '074190'
GROUP BY PROJECT, TYPE;
Original:
PROJECT      TYPE          OBS           
074190      ASBESTOS     26326.99     
074190        CM             0 
Current Result:
PROJECT      TYPE         OBS            
074190        CM           0               
074190      CONTIN      26326.99   

Answer 1

如果我理解正确，您可以使用窗口函数来计算每个项目的"CONTIN"数量，然后将该信息用于聚合键：

SELECT PROJECT, 
(CASE WHEN NUM_CONTIN = 0 AND TYPE = 'ASBESTOS' THEN 'CONTIN' ELSE TYPE
END) as TYPE
SUM(OBLIGATION) AS OBS 
FROM (SELECT om.*,
SUM(CASE WHEN TYPE = 'CONTIN' THEN 1 ELSE 0 END) OVER (PARTITION BY PROJECT) as NUM_CONTIN
FROM OBS_MASTER om
) om
WHERE PROJECT = '074190'
GROUP BY PROJECT,
(CASE WHEN NUM_CONTIN = 0 AND TYPE = 'ASBESTOS' THEN 'CONTIN' ELSE TYPE
END)

Answer 2

这是另一个选项：

SQL> with
2  obs_master (project, type, obligation) as
3    -- sample data
4    (select 1, 'ASBESTOS', 2500 from dual union all
5     select 1, 'CONTIN'  , 1500 from dual union all
6     select 1, 'CM'      ,  500 from dual union all
7     --
8     select 2, 'ASBESTOS', 1000 from dual union all
9     select 2, 'CM'      , 1000 from dual
10    ),
11  temp as
12    -- check whether project has CONTIN type
13    (select project, min(type) cb_contin
14     from obs_master
15     group by project, type
16     having type = 'CONTIN'
17    )
18  select a.project,
19         -- if type = ASBESTOS and that project doesn't contain CONTIN,
20         -- replace ASBESTOS with CONTIN
21         case when a.type = 'ASBESTOS' and
22                   b.cb_contin <> 'CONTIN'
23              then 'CONTIN'
24              else a.type
25         end type,
26         sum(a.obligation) obs
27  from obs_master a left join temp b on a.project = b.project
28  group by a.project,
29         case when a.type = 'ASBESTOS' and
30                   b.cb_contin <> 'CONTIN'
31              then 'CONTIN'
32              else a.type
33         end
34  order by a.project;
PROJECT TYPE            OBS
---------- -------- ----------
1 ASBESTOS       2500
1 CM              500
1 CONTIN         1500
2 ASBESTOS       1000
2 CM             1000
SQL>

Answer 3

如果您的目标只是更新类型值，那么类似于以下语句的内容可能会起作用：

UPDATE OBS_MASTER
SET TYPE='CONTIN'
WHERE TYPE='ASBESTOS' and 
PROJECT not in 
(select distinct PROJECT 
from OBS_MASTER 
where TYPE='CONTIN')

希望这有帮助。