sql - 每组的结果

Question

更新:

is_active = false后面可以有is_active = true，所以我需要获取最后赋值的is_active = true，获取前面的is_active = false记录

我有一个具有这种结构的表

id (not pk but each group is incremental), name, grp, is_active (boolean)

第 1 组有这样的数据

1, name1, group1, true
2, name2, group1, true
3, name3, group1, false
4, name1, group1, false
5, name2, group1, true
6, name3, group1, false <-- this is the next assigned id as the preceding record has an is_active = true
7, (names will differt, group the same and all false)...

...更多数据在同一个表中，但对于 group2

100, name1, group1, true
101, name2, group1, true
102, name3, group1, true
103, name1, group1, true
104, name2, group1, true
105, name3, group1, false <-- this is the next assigned id as the preceding record has an is_active = true
106, (names will differt, group the same and all false)...

我有这个，但它没有像我希望的那样工作

SELECT grp, COUNT(*)
FROM tbl_1
WHERE is_active = false
AND id > (
    SELECT id
    FROM tbl_1
    WHERE is_active = true
    ORDER BY id DESC
    LIMIT 1
)
GROUP BY grp

所以我想要的返回是:

group1, 6
group2, 105

但我只得到

group2, 105

Answer 1

您真的只需要找到第一个 id，其中 is_active = false。没有必要在 id 上应用“大于第一个事件”条件。

SELECT MIN(id), grp
  FROM tbl_1
  WHERE is_active = false
  GROUP BY grp

更新:

更新问题的答案:

SELECT min(id), grp
  FROM tbl_1 t
  WHERE is_active = false
  AND id > (SELECT max(id) 
              FROM tbl_1 
              WHERE is_active = true 
                AND grp = t.grp)
  GROUP BY grp;