DECLARE @cids VARCHAR(100)
SET @cids='英语,德语'SELECT sid FROM stuLinkCla a
INNER JOIN class b
ON a.cid=b.id
WHERE CHARINDEX(',' + cName + ',',',' + @cids + ',')>0
GROUP BY sid
HAVING COUNT(*)=LEN(@cids) - LEN(REPLACE(@cids,',','')) + 1上面是聚合法,exists写太多了,就不写了。随手敲的,可能手误本质同此贴http://topic.csdn.net/u/20081111/11/5d665b4a-5013-46df-97b0-61026f0ab4ac.html
SET @cids='英语,德语'SELECT sid FROM stuLinkCla a
INNER JOIN class b
ON a.cid=b.id
WHERE CHARINDEX(',' + cName + ',',',' + @cids + ',')>0
GROUP BY sid
HAVING COUNT(*)=LEN(@cids) - LEN(REPLACE(@cids,',','')) + 1上面是聚合法,exists写太多了,就不写了。随手敲的,可能手误本质同此贴http://topic.csdn.net/u/20081111/11/5d665b4a-5013-46df-97b0-61026f0ab4ac.html
(
select distinct sid from stuLinkCla a , class b where a.cid = b.id and b.cname = '英语'
union all
select distinct sid from stuLinkCla a , class b where a.cid = b.id and b.cname = '德语'
) t
group by sid having count(*) = 2如果第一表要用(个人觉得有点多此一举)select id from student where id in
(
select sid from
(
select distinct sid from stuLinkCla a , class b where a.cid = b.id and b.cname = '英语'
union all
select distinct sid from stuLinkCla a , class b where a.cid = b.id and b.cname = '德语'
) t
group by sid having count(*) = 2
)