有一张中间表,两个字段projectId,appId
projectId appId
p1 a1
p2 a1
p2 a2
p2 a3
p3 a1
p3 a2要求根据已知的appId(比如a1,a2)查询出只包含该appId集合的porjectId,小弟在此请教这个sql该怎么写啊?
projectId appId
p1 a1
p2 a1
p2 a2
p2 a3
p3 a1
p3 a2要求根据已知的appId(比如a1,a2)查询出只包含该appId集合的porjectId,小弟在此请教这个sql该怎么写啊?
from 中间表 a
where not exists (select 1 from 中间表 where projectId=a.projectId and appId !='a1' or appId !='a2');
select *
from t_RichardQi a
where not exists (select 1 from t_RichardQi where projectId=a.projectId and appId !='a1' and appId !='a2');或者select *
from t_RichardQi a
where not exists (select 1 from t_RichardQi where projectId=a.projectId and appId not in ('a1','a2'));
+-----------+-------+
| projectId | appId |
+-----------+-------+
| p1 | a1 |
| p2 | a1 |
| p2 | a2 |
| p2 | a3 |
| p3 | a1 |
| p3 | a2 |
+-----------+-------+
6 rows in set (0.00 sec)mysql>
mysql> select *
-> from t_RichardQi a
-> where not exists (select 1 from t_RichardQi where projectId=a.projectId a
nd appId !='a1' and appId !='a2');
+-----------+-------+
| projectId | appId |
+-----------+-------+
| p1 | a1 |
| p3 | a1 |
| p3 | a2 |
+-----------+-------+
3 rows in set (0.00 sec)mysql> select *
-> from t_RichardQi a
-> where not exists (select 1 from t_RichardQi where projectId=a.projectId a
nd appId not in ('a1','a2'));
+-----------+-------+
| projectId | appId |
+-----------+-------+
| p1 | a1 |
| p3 | a1 |
| p3 | a2 |
+-----------+-------+
3 rows in set (0.00 sec)mysql>
select distinct projectid
from test a
where a.appid in('a1','a2') and
2=(select count(distinct appid) from test where projectid=a.projectid)
将ACMAIN_CHM的方法增加group by projectId和 having count(*)=2就正确了,谢谢两位了