sid scid
1 1
1 2
1 3
1 4
1 5
2 1
2 6
2 7
2 8
3 1
3 9
3 10
4 1
4 11
4 12
4 13
4 14
求sid 1,2,3,4都共有的scid
谢谢。
第一个念头，使用intersect关键字求交集
select scid from t_test where sid=1
intersect
select scid from t_test where sid=2
intersect
select scid from t_test where sid=3
intersect
select scid from t_test where sid=4
当然，也可以使用子查询
select distinct scid from t_test where scid in (select scid from t_test where sid=1)
and scid in (select scid from t_test where sid=2)
and scid in (select scid from t_test where sid=3)
and scid in (select scid from t_test where sid=4)