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

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

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

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

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了。

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

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
即最终计数值=条件长度的班级信息,此即我们要查找的班级

使用sql来实现,那么整个实现的sql如下所示:

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不计数的特点。
此文参考了以下文章
http://www.itpub.net/thread-1169213-3-1.html(如何判断多个集合相等,包含)

转载请标明出处:i flym
本文地址:https://www.iflym.com/index.php/datastructure/201202050001.html

相关文章:

作者: flym

I am flym,the master of the site:)

《一个实现类似find contains many(many in many)的sql 语句》有一个想法

  1. 如果存在重名,上面的SQL就不行了。最好可以加一下去重。
    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(distinct (case when b.name in (‘张三’,’李四’) then b.name end))=2
    )

发表评论

电子邮件地址不会被公开。 必填项已用*标注