MySQL依靠先决条件吗? [英] MySQL count on precondition?

问题描述

请考虑以下方案

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"?

How to find the number of unique user, which after the row 'tag1' there is a 'tag2' followed in future row of that user?

根据上述查询，答案为 2 (foo，bar)

The answer is 2 (foo,bar), based on the above query

推荐答案

您可以使用子查询查找此类行:

You can use a sub query to find such rows:

SELECT *
FROM yourdata AS t
WHERE tag = 'tag1'
AND EXISTS (
    SELECT 1
    FROM yourdata AS x
    WHERE x.user = t.user
    AND x.tag = 'tag2'
    AND (x.created > t.created OR x.id > t.id)
)

这篇关于MySQL依靠先决条件吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！