如何基于LEFT JOIN结果生成动态状态字符串列？

当然可以实现这个需求，下面提供两种可行的方法，同时修正你原SQL中的一处笔误（最后一个LEFT JOIN的关联条件里vip.cid应为vipstat.cid）：

方法一：基于原有查询直接修改（CASE WHEN） #

直接在原有查询的SELECT子句中使用CASE WHEN语句，按优先级匹配布尔值并返回对应的状态字符串：

SELECT
    c.cid,
    CASE
        WHEN newcust.newcust THEN 'newcust'
        WHEN svipstat.svipstat THEN 'svipstat'
        WHEN vipstat.vipstat THEN 'vipstat'
        ELSE NULL -- 可根据需求替换为默认值，例如'普通客户'
    END AS status
FROM customer c
LEFT JOIN (
    SELECT cid, true AS newcust
    FROM customer c
    WHERE active IS TRUE AND created_at >= current_date - interval '30 days'
) AS newcust ON newcust.cid = c.cid
LEFT JOIN (
    SELECT c.cid, true AS svipstat
    FROM customer c
    WHERE active IS TRUE 
      AND (SELECT count(1) FROM visits v WHERE v.cid = c.cid AND v.vdate >= current_date - interval '30 days') 
          >= (SELECT value FROM criteria WHERE c = 'svipstat' AND pid = c.pid)
) AS svipstat ON svipstat.cid = c.cid
LEFT JOIN (
    SELECT c.cid, true AS vipstat
    FROM customer c
    WHERE active IS TRUE 
      AND (SELECT count(1) FROM visits v WHERE v.cid = c.cid AND v.vdate >= current_date - interval '30 days') 
          >= (SELECT value FROM criteria WHERE c = 'vipstat' AND pid = c.pid)
) AS vipstat ON vipstat.cid = c.cid;

说明： #

• CASE WHEN会按顺序匹配条件，一旦找到第一个为true的布尔列，就返回对应的状态字符串，这和你提供的示例逻辑一致（每个客户仅对应一个状态）。
• 如果需要调整状态优先级，只需修改CASE WHEN的判断顺序即可。

方法二：优化查询性能（使用CTE减少冗余计算） #

原查询中多次重复计算客户30天内的访问次数，使用CTE（公共表表达式）预先计算这些值，能大幅提升查询效率，同时让逻辑更清晰：

WITH customer_visit_stats AS (
    SELECT 
        c.cid,
        c.active,
        c.created_at,
        COUNT(v.cid) AS visit_count_30d,
        -- 预先获取svip和vip的阈值
        (SELECT value FROM criteria WHERE c = 'svipstat' AND pid = c.pid) AS svip_threshold,
        (SELECT value FROM criteria WHERE c = 'vipstat' AND pid = c.pid) AS vip_threshold
    FROM customer c
    LEFT JOIN visits v 
        ON v.cid = c.cid 
        AND v.vdate >= current_date - interval '30 days'
    GROUP BY c.cid, c.active, c.created_at
)
SELECT
    cid,
    CASE
        WHEN active IS TRUE AND created_at >= current_date - interval '30 days' THEN 'newcust'
        WHEN active IS TRUE AND visit_count_30d >= svip_threshold THEN 'svipstat'
        WHEN active IS TRUE AND visit_count_30d >= vip_threshold THEN 'vipstat'
        ELSE NULL
    END AS status
FROM customer_visit_stats;

说明： #

• CTEcustomer_visit_stats一次性计算出每个客户的30天访问次数、活跃状态、创建时间以及对应的会员阈值，避免了重复的子查询调用。
• 同样通过CASE WHEN按优先级生成状态字符串，逻辑和方法一完全一致，但性能更优，尤其当数据量较大时。

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