一个实现类似find contains many(many in many)的sql 语句

有如下一种需求(使用班级class和学生student表来描述)

1	找到学生中即有名字叫张三也有名字叫李四的班级，其中参数<名字>表示任意多个名字，即不限仅有两个参数。

在这种需求中，如果仅只有张三和李四两个条件，则sql可以写成如下:

1 2 3

select a.* from class a where exists(select 1 from student where classId=a.id and name=张三) and exists(select 1 from student where classid=a.id and name=李四)

在以上的条件中，有两个条件，因此有两个exists子句。而如果有更多呢，比如三个或四个以上，那么 这个exists就会更多。在使用以java实现的sql语句中，就需要使用程序(如for循环）来组装sql了。

有一种更好的解决办法如下，即类似一种 (张三,李四）均在指定班级的学生列表中这种理解方式。使用伪码来描述就是

1 2 3

select a.* rom class a inner join a.studentList where a.studentList.names contains(张三，李思） 或 select a.* rom class a inner join a.studentList where (张三，李思）in a.studentList.names

就是这种集合之间包含的例子，即保证一个集合在另一个集合中。然而现在的sql还没有能够直接表示这种的，更多的使用是使用in来表示一个参数值在一个集合中，而不是一个子集合包含一系列指定的参数。

那么反过来呢，我们利用in来处理这种问题，当学生有一个名字满足参数中值的时候就+1，那么符合条件的班级中的对学生计数的值一定就等于参数列表的长度了（这里必须假设参数值是不相同的）。简单的逻辑如下所示：

1 2 3 4

对每一个班级进行分组 对每一个班级中的学生进行处理 当学生中的名字满足条件，计数值+1 即最终计数值＝条件长度的班级信息，此即我们要查找的班级

使用sql来实现，那么整个实现的sql如下所示：

1 2 3 4 5

select * from class where id in (     select a.id from class a inner join student b on b.classId = a.id     group by a.id     having count(case b.name in (张三，李四) then 1 end)=2 )

以上sql在oracle 10g下测试通过，这里利用了count只对有值的数据计数，而对null不计数的特点。