一、逗号分隔的字符串多值查询
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" 的行