一、逗号分隔的字符串多值查询
1，背景
假设有一个表location_type_relation，其中有1个字段location_ids，用逗号分隔了多个标签，还有1个字段type_ids，也是用逗号分隔了多个标签
2，需求
现在要判断location_ids这个字段是否包含字符串’1’或 ‘2’或’4’，并且type_ids这个字段是否包含字符串’1’或 ‘2’或’11’
3，实现
分别是postgresql和oracle两种高效的sql语句
// Postgresql
SELECT *
FROM location_type_relation mt
WHERE EXISTS (SELECT 1FROM unnest(string_to_array(mt.location_ids, ',')) AS locationWHERE location = '1'OR location = '2'OR location = '4'
)
AND EXISTS (SELECT 1FROM unnest(string_to_array(mt.type_ids, ',')) AS typeWHERE type = '1'OR type = '2'OR type = '11'
);
查询结果如下
以下是Oracle语句
// Oracle格式
SELECT *
FROM location_type_relation mt
WHERE EXISTS (SELECT 1FROM (SELECT TO_NUMBER(REGEXP_SUBSTR(mt.location_ids, '[^,]+', 1, LEVEL)) AS locationFROM DUALCONNECT BY REGEXP_SUBSTR(mt.location_ids, '[^,]+', 1, LEVEL) IS NOT NULL)WHERE location IN (1, 2, 4)
)
AND EXISTS (SELECT 1FROM (SELECT TO_NUMBER(REGEXP_SUBSTR(mt.type_ids, '[^,]+', 1, LEVEL)) AS typeFROM DUALCONNECT BY REGEXP_SUBSTR(mt.type_ids, '[^,]+', 1, LEVEL) IS NOT NULL)WHERE type IN (1, 2, 11)
);
二、查询三级位置
1，背景
假设有一个树形结构表location，其中有1个字段parent_id，存放的是当前位置的上级位置id，node_name代表本级位置名称，parent_ids代表当前位置的所有上级位置id
还有一个工程师表engineer，有一个子段location_id，存放的该工程师所属位置，另一个字段username代表该工程师名字
2，需求
现在的需求是要去找到每个工程师的上面两级位置，即所属省和所属市，并用/拼接起来
3，实现
多次关联即可
select
eng.username,
location3.node_name || '/' || location2.node_name || '/' || location1.node_name locationName
from engineer eng
left join location location1 on eng.location_id = location1.id
left join location location2 on location1.parent_id = location2.id
left join location location3 on location2.parent_id = location3.id
三、局部更新
有一个物品表goods，里面有多条数据，存的物品编码和物品名称等，现在发现有一批物品编码导入库的时候导错了，比如编码为3205AAA0001，实际应为3205BBB0001，同理3205AAA0002，实际应为3205BBB0002，以此类推，这批数据大概有几百个
为此必须要实现批量更新，sql如下
UPDATE goods
SET device_code = REPLACE(device_code, 'AAA', 'BBB')
WHERE device_code LIKE '%AAA%'; -- 只更新包含 "AAA" 的行
