我试了几个小时，读了很多帖子，但我仍然不知道如何处理这个请求
我有这样的桌子
+-------+--------+----------+------------+-----------+
|cust_id|date_ |order_rank|cust_segment|segment_new|
+-------+--------+----------+------------+-----------+
|10 |11-2021 | 1 | ORG | ORG |
+-------+--------+----------+------------+-----------+
|10 |11-2021 | 2 | ORG | ORG |
+-------+--------+----------+------------+-----------+
|10 |12-2021 | 3 | ORG | ORG |
+-------+--------+----------+------------+-----------+
|20 |10-2021 | 1 | SE | SE |
+-------+--------+----------+------------+-----------+
|20 |11-2021 | 2 | ORG | SE |
+-------+--------+----------+------------+-----------+
|20 |12-2021 | 3 | ORG | SE |
+-------+--------+----------+------------+-----------+
|30 |10-2021 | 1 | ORG | ORG |
+-------+--------+----------+------------+-----------+
|30 |10-2021 | 2 | SE | ORG |
+-------+--------+----------+------------+-----------+
|30 |11-2021 | 3 | SE | ORG |
+-------+--------+----------+------------+-----------+
我想要的是如果第一个"；order_rank"是ORG，但在"ORG"的其余部分中；cust_ segment"；在相同的"；cust_ id"；如果存在分段SE，则我想改变所有"；segment_new"；在相同的"；cust_ id"；进入下方类似SE的表格
+-------+--------+----------+------------+-----------+
|cust_id|date_ |order_rank|cust_segment|segment_new|
+-------+--------+----------+------------+-----------+
|10 |11-2021 | 1 | ORG | ORG |
+-------+--------+----------+------------+-----------+
|10 |11-2021 | 2 | ORG | ORG |
+-------+--------+----------+------------+-----------+
|10 |12-2021 | 3 | ORG | ORG |
+-------+--------+----------+------------+-----------+
|20 |10-2021 | 1 | SE | SE |
+-------+--------+----------+------------+-----------+
|20 |11-2021 | 2 | ORG | SE |
+-------+--------+----------+------------+-----------+
|20 |12-2021 | 3 | ORG | SE |
+-------+--------+----------+------------+-----------+
|30 |10-2021 | 1 | ORG | SE |
+-------+--------+----------+------------+-----------+
|30 |10-2021 | 2 | SE | SE |
+-------+--------+----------+------------+-----------+
|30 |11-2021 | 3 | SE | SE |
+-------+--------+----------+------------+-----------+
我该怎么做？如果有人知道，请告诉我…谢谢
您的问题文本与示例结果冲突，因此我建议使用2个查询，其中1个符合文本，另一个符合预期结果。
这符合问题文本if the first "order_rank" is ORG but in the rest of "cust_segment" in same "cust_id" are segment SE is exist then I want to change all "segment_new" in same "cust_id" into SE
update data1
set segment_new = (case when order_rank = 1
and cust_segment = 'ORG'
and exists (select 1 from data1 cs
where data1.cust_id = cs.cust_id
and cs.order_rank > 1
and cs.cust_segment = 'SE')
then 'SE'
else cust_segment
end)
;
这样做的结果是：
cust_id date_ order_rank cust_segment segment_new
--------- --------- ------------ -------------- -------------
10 11-2021 1 ORG ORG
10 11-2021 2 ORG ORG
10 12-2021 3 ORG ORG
20 10-2021 1 SE SE
20 11-2021 2 ORG ORG
20 12-2021 3 ORG ORG
30 10-2021 1 ORG SE
30 10-2021 2 SE SE
30 11-2021 3 SE SE
以匹配问题的预期结果表：
update data1
set segment_new = (case when cust_id in(select cust_id from data1 where cust_segment = 'SE')
then 'SE'
else cust_segment
end)
;
结果是：
cust_id date_ order_rank cust_segment segment_new
--------- --------- ------------ -------------- -------------
10 11-2021 1 ORG ORG
10 11-2021 2 ORG ORG
10 12-2021 3 ORG ORG
20 10-2021 1 SE SE
20 11-2021 2 ORG SE
20 12-2021 3 ORG SE
30 10-2021 1 ORG SE
30 10-2021 2 SE SE
30 11-2021 3 SE SE
sqlfiddle用于两个查询