mysql - sql组具有不超过2个不同的特定值

我有桌子
原始数据就像

RollNumber | Subject | G         | Part | Status
------------------------------------------------
1          | 1       | 1         | 1    |  1
1          | 1       | 1         | 2    |  1
1          | 2       | 1         | 1    |  1
1          | 2       | 1         | 2    |  5
1          | 3       | 1         | 1    |  1
1          | 3       | 1         | 2    |  1
2          | 1       | 2         | 1    |  1
2          | 1       | 2         | 2    |  1
2          | 2       | 2         | 1    |  1
2          | 2       | 2         | 2    |  1
2          | 3       | 2         | 1    |  1
2          | 3       | 2         | 2    |  1
3          | 1       | 2         | 1    |  1
3          | 1       | 2         | 2    |  1
3          | 2       | 2         | 1    |  1
3          | 2       | 2         | 2    |  1
3          | 3       | 2         | 1    |  0
3          | 3       | 2         | 2    |  1
4          | 1       | 2         | 1    |  1
4          | 1       | 2         | 2    |  1
4          | 2       | 2         | 1    |  1
4          | 2       | 2         | 2    |  1
4          | 3       | 2         | 1    |  3
4          | 3       | 2         | 2    |  1

我想要所有应该具有不同状态1和3的RollNumber数据
我所需的数据如下：

RollNumber | Subject | G         | Part | Status
------------------------------------------------
4          | 1       | 2         | 1    |  1
4          | 1       | 2         | 2    |  1
4          | 2       | 2         | 1    |  1
4          | 2       | 2         | 2    |  1
4          | 3       | 2         | 1    |  3
4          | 3       | 2         | 2    |  1

我需要一个适用于所有sql的查询，也适用于mysql

最佳答案

您可以使用聚合在子查询中查找同时具有1、3作为状态的滚动号，并使用该滚动号获取所有相关行。

使用IN：

select *
from t
where rollnumber in (
        select rollnumber
        from t
        where status in (1, 3)
        group by rollnumber
        having count(distinct status) = 2
        );

使用JOIN：

select t1.*
from t t1
join (
    select rollnumber
    from t
    where status in (1, 3)
    group by rollnumber
    having count(distinct status) = 2
    ) t2 on t1.rollnumber = t2.rollnumber;

子查询使用where子句过滤以仅保留状态为1或3的行。然后，在对rollNumber分组时，我们检查不同的状态计数是否为2，这意味着该rollNumber的1和3都存在。