SQL从表内容条件满足查询

SQL从表内容中，找出满足条件的主表查询，这个东西还不好说：
Class表
----------
id   name
1    班级1
2    班级2
3    班级3
Student表
id classid  name
----------------------------
1 1  李四
2 1  李三
3  1  张三
4 2  李四
5 2  张三
6 3  王五
7 3  李三
现在要找出有班上同时有（李四和张三）班级

--创建表
CREATE TABLE Class
(
    id int,
    name nvarchar(200)
)
GO
CREATE TABLE Student
   ( 
    id    int,
    classid int,
    name    nvarchar(100)
   )
GO
--插入数据
INSERT INTO Class(id,name)Values(1,'班级1')
INSERT INTO Class(id,name)Values(2,'班级2')
INSERT INTO Class(id,name)Values(3,'班级3')
INSERT INTO Student(id,classid,name)Values(1,1,'李四')
INSERT INTO Student(id,classid,name)Values(2,1,'李三')
INSERT INTO Student(id,classid,name)Values(3,1,'张三')
INSERT INTO Student(id,classid,name)Values(4,2,'李四')
INSERT INTO Student(id,classid,name)Values(5,2,'张三')
INSERT INTO Student(id,classid,name)Values(6,3,'王五')
INSERT INTO Student(id,classid,name)Values(7,3,'李三')
--查询
SELECT Class.name FROM Class INNER JOIN Student ON Class.id = Student.classid
WHERE EXISTS(SELECT * FROM Student WHERE [name] = '李四' AND  Class.id = Student.classid) 
      AND EXISTS(SELECT * FROM Student WHERE [name] = '张三' AND  Class.id = Student.classid) 
GROUP BY Class.name
--删除表
DROP TABLE Class
DROP TABLE Student

EXISTS 是判断是否存在,和in类似,但效率要比in高

SELECT * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  EXISTS (SELECT ‘X'  FROM DEPT  WHERE  DEPT.DEPTNO = EMP.DEPTNO  AND  LOC = ‘MELB')

SELECT  * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  DEPTNO IN(SELECT DEPTNO  FROM  DEPT  WHERE  LOC = ‘MELB')

这两句效果一样

转载于:https://www.cnblogs.com/xiaocai0923/archive/2011/09/20/2181984.html