优化含多个相似子查询的SELECT语句问题咨询

我看了你写的查询和需求，原查询嵌套子查询太多，逻辑也没关联到「与flag关联的name相关的其他name」，导致结果不符合预期。下面给你简化后的查询，完全匹配你的需求：

简化后的查询语句 #

SELECT 
    CASE 
        WHEN e.name = 'jack' AND e.flag = '22' THEN 'jack (flag 22)'
        ELSE 'other names'
    END AS name_category,
    COUNT(*) AS count,
    SUM(e.time_spent_millis) AS total_time
FROM event e
WHERE e.host = 'mantis'
  AND e.template IN ('wrap')
  AND e.timestamp BETWEEN 1559337058633 AND 1559683282607
  AND e.id IN (
    -- 筛选出所有符合条件的jack所在的id
    SELECT DISTINCT id
    FROM event
    WHERE host = 'mantis'
      AND template IN ('wrap')
      AND timestamp BETWEEN 1559337058633 AND 1559683282607
      AND name = 'jack'
      AND (
        (flag = '22')
        OR 
        (flag IN ('0', '1') 
         AND id NOT IN (
             SELECT id 
             FROM event 
             WHERE host = 'mantis'
               AND template IN ('wrap')
               AND timestamp BETWEEN 1559337058633 AND 1559683282607
               AND name = 'jack'
               AND flag = '22'
         ))
      )
  )
GROUP BY name_category;

逻辑解释 #

1. 内层子查询：先锁定所有符合你条件的jack记录对应的id——要么是flag='22'的jack，要么是flag为0/1且同id下没有flag='22'的jack的记录。
2. 外层查询：用这些id去匹配原表的所有记录，这样就能拿到每个符合条件的id下的所有关联name（包括jack和其他用户）。
3. 分组统计：通过CASE把flag='22'的jack单独归为一类，其他所有name归为另一类，然后分别统计数量和总耗时，正好得到你要的结果：
   • jack (flag 22)的计数为2
   • other names的计数为4
   • total_time为所有数据的时间总和

原查询的问题点 #

原查询的OR逻辑只是把两个子查询的id合并，但没有关联到同id下的其他name；而且按id, name分组会导致每个id+name单独统计，没法得到你需要的汇总结果。

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