如何简化/缩短已正常运行的SELECT查询语句？

嘿，我注意到你这条查询因为重复使用replace(regexp_substr(...))逻辑变得特别冗长，确实很影响可读性和维护性。这里有几个实用的简化方案，你可以根据自己的数据库环境和需求来选择：

方案1：用CTE拆分键值对后转列（最易扩展） #

如果你的数据库支持XML或表值函数（比如Oracle、PostgreSQL），可以先把properties字段拆成一行行的键值对，再通过聚合函数转回列。这样只需要写一次拆分逻辑，后续加新字段只需要在SELECT里补充对应规则即可：

WITH parsed_properties AS (
    SELECT 
        o.id,
        o.name,
        -- 拆分键和值
        REGEXP_SUBSTR(prop_item, '[^=]+', 1, 1) AS prop_key,
        REGEXP_SUBSTR(prop_item, '[^=]+', 1, 2) AS prop_value
    FROM OFFER o,
         -- 按分号拆分properties为多行
         TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('<items><item>' || REPLACE(o.properties, ';', '</item><item>') || '</item></items>'), '/items/item'))) p(prop_item)
    WHERE o.name = 'PLAN 599'
)
SELECT 
    id,
    name,
    MAX(CASE WHEN prop_key = 'EntityID' THEN prop_value END) AS EntityID,
    MAX(CASE WHEN prop_key = 'deployed' THEN prop_value END) AS deployed,
    MAX(CASE WHEN prop_key = 'type' THEN prop_value END) AS type,
    MAX(CASE WHEN prop_key = 'level' THEN prop_value END) AS "LEVEL",
    MAX(CASE WHEN prop_key = 'description' THEN prop_value END) AS description,
    MAX(CASE WHEN prop_key = 'indicator' THEN prop_value END) AS indicator,
    MAX(CASE WHEN prop_key = 'Agreement' THEN prop_value END) AS Agreement,
    MAX(CASE WHEN prop_key = 'Activation date to charge' THEN prop_value END) AS Activationdatetocharge,
    MAX(CASE WHEN prop_key = 'id' THEN prop_value END) AS id,
    MAX(CASE WHEN prop_key = 'name' THEN prop_value END) AS name,
    MAX(CASE WHEN prop_key = 'currencyCode' THEN prop_value END) AS currencyCode,
    MAX(CASE WHEN prop_key = 'saleExpirationDate' THEN prop_value END) AS saleExpirationDate,
    MAX(CASE WHEN prop_key = 'Product type' THEN prop_value END) AS Producttype,
    MAX(CASE WHEN prop_key = 'saleEffectiveDate' THEN prop_value END) AS saleEffectiveDate,
    MAX(CASE WHEN prop_key = 'Deactivation date to charge' THEN prop_value END) AS Deactivationdatetocharge
    -- 新增字段直接在这里加对应的CASE语句即可
FROM parsed_properties
GROUP BY id, name;

方案2：封装自定义函数（最简洁） #

如果你的数据库允许创建自定义函数，把重复的提取逻辑封装成函数，查询语句会瞬间清爽很多，后续维护也只需要修改函数即可：

先创建函数（以Oracle为例） #

CREATE OR REPLACE FUNCTION extract_prop(p_properties VARCHAR2, p_key VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
    RETURN REGEXP_SUBSTR(p_properties, p_key || '=([^;]*)', 1, 1, NULL, 1);
END;
/

简化后的查询 #

SELECT /*GenInfo*/ 
    id,
    name,
    extract_prop(properties, 'EntityID') AS EntityID,
    extract_prop(properties, 'deployed') AS deployed,
    extract_prop(properties, 'type') AS type,
    extract_prop(properties, 'level') AS "LEVEL",
    extract_prop(properties, 'description') AS description,
    extract_prop(properties, 'indicator') AS indicator,
    extract_prop(properties, 'Agreement') AS Agreement,
    extract_prop(properties, 'Activation date to charge') AS Activationdatetocharge,
    extract_prop(properties, 'id') AS id,
    extract_prop(properties, 'name') AS name,
    extract_prop(properties, 'currencyCode') AS currencyCode,
    extract_prop(properties, 'saleExpirationDate') AS saleExpirationDate,
    extract_prop(properties, 'Product type') AS Producttype,
    extract_prop(properties, 'saleEffectiveDate') AS saleEffectiveDate,
    extract_prop(properties, 'Deactivation date to charge') AS Deactivationdatetocharge
    -- 新增字段直接调用函数即可
FROM OFFER 
WHERE name = 'PLAN 599';

方案3：优化正则写法（轻量简化） #

如果不想用CTE或函数，也可以直接优化正则表达式，利用捕获组直接提取值，省去外层的replace函数，让代码更简洁：

SELECT /*GenInfo*/ 
    id,
    name,
    REGEXP_SUBSTR(properties, 'EntityID=([^;]*)', 1, 1, NULL, 1) AS EntityID,
    REGEXP_SUBSTR(properties, 'deployed=([^;]*)', 1, 1, NULL, 1) AS deployed,
    REGEXP_SUBSTR(properties, 'type=([^;]*)', 1, 1, NULL, 1) AS type,
    REGEXP_SUBSTR(properties, 'level=([^;]*)', 1, 1, NULL, 1) AS "LEVEL",
    REGEXP_SUBSTR(properties, 'description=([^;]*)', 1, 1, NULL, 1) AS description,
    REGEXP_SUBSTR(properties, 'indicator=([^;]*)', 1, 1, NULL, 1) AS indicator,
    REGEXP_SUBSTR(properties, 'Agreement=([^;]*)', 1, 1, NULL, 1) AS Agreement,
    REGEXP_SUBSTR(properties, 'Activation date to charge=([^;]*)', 1, 1, NULL, 1) AS Activationdatetocharge,
    REGEXP_SUBSTR(properties, 'id=([^;]*)', 1, 1, NULL, 1) AS id,
    REGEXP_SUBSTR(properties, 'name=([^;]*)', 1, 1, NULL, 1) AS name,
    REGEXP_SUBSTR(properties, 'currencyCode=([^;]*)', 1, 1, NULL, 1) AS currencyCode,
    REGEXP_SUBSTR(properties, 'saleExpirationDate=([^;]*)', 1, 1, NULL, 1) AS saleExpirationDate,
    REGEXP_SUBSTR(properties, 'Product type=([^;]*)', 1, 1, NULL, 1) AS Producttype,
    REGEXP_SUBSTR(properties, 'saleEffectiveDate=([^;]*)', 1, 1, NULL, 1) AS saleEffectiveDate,
    REGEXP_SUBSTR(properties, 'Deactivation date to charge=([^;]*)', 1, 1, NULL, 1) AS Deactivationdatetocharge
FROM OFFER 
WHERE name = 'PLAN 599';

这里利用了REGEXP_SUBSTR的第6个参数（捕获组索引），直接提取等号后的内容，省去了replace的步骤，虽然还是重复调用函数，但代码长度和可读性都有提升。

内容的提问来源于stack exchange，提问作者Gabriel