MySQL依赖于前提条件吗？

Question

考虑下面的方案

id   user   tag    created
1    foo    tag1   2018-09-01
2    foo    tag2   2018-09-01
3    bar    tag1   2018-09-02
4    bar    tag2   2018-09-03
5    kkk    tag2   2018-09-05
6    qqq    tag1   2018-09-12

如何查找唯一用户的数量，该用户在“tag1”行之后有一个“tag2”，该用户的 future 行后面跟着“tag2”？

根据上面的查询，答案是2 (foo,bar)

Answer 1

您可以使用聚合来获取用户列表:

select user
from t
group by user
having min(case when tag = 'tag1' then created end) < max(case when tag = 'tag2' then created end);

要获取此类用户的数量，请使用子查询:

select count(*)
from (select user
      from t
      group by user
      having min(case when tag = 'tag1' then created end) < max(case when tag = 'tag2' then created end)
     ) u;