标签：having

一个实现类似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（如何判断多个集合相等,包含)

hibernate中的qbc(query by criteria)不支持having

    最近在做一次查询时，需要对查询的结果进行二次过滤，即分组之后再过滤，这种情况下，需要使用having。如以下的例子，我需要查询一个字段和另一个字段的和，并对记录进行分组，但结果集中并不需要总记录数为0的结果。sql语句如下所示：

select a, sum(b) as bsum from t where clause1=? group by a having and sum(b) > 0

    这种查询语句，使用hql能够很好地写出来，并且能够很好的运行。但如果条件数不定时，使用hql就需要动态的拼接hql，并且需要根据参数来设置参数。如下所示：

if(StringUtils.hasText("clause1"))
			hql += "clause1 = :clause1";
		//.....其它判断和处理
		if(StringUtils.hasText("clause1"))
			query.setString("clause1", clause1);

    在这种情况下，许多的if判断，无论是在编码还是在逻辑处理上都让人麻烦。对于不定的参数查询，qbc是最合适的（当然还有qbe）。但是对于qbc，处理having的这种情况即是不能处理的。
    不管使用任何处理情况，都不能从criteria中构建出这个having子句。

    经过google之后，网上有一个hibernate的bug报告HHH-1043,http://opensource.atlassian.com/projects/hibernate/browse/HHH-1043 。这个链接中，论述了在hibernate中的问题，并且提供一些解决方案，但这些解决方案都不能很容易的理解和解决问题。

    最根本的问题在于，qbc中的criteria，在hibernate内部设计和编码时，都是作为where条件中的一部分来实现的.主要的代码在类CriteriaQueryTranslator的方法getWhereCondition中，在这个方法中，迭代所有的criteria，并作为where条件使用。作为用在group集合中的having子句，并不属于where条件中任何一句。如果强行在criteria中使用having（比如使用Restrictions.sqlRestriction)，最终的结果就是将having最终加到了where中条件的一部分。不过，这样生成的sql，即是错的，不会得到任何结果。

    直到最新的hibernate 3.6版本，此问题仍然未解决。对于需要处理聚集函数的条件问题，现阶段，除了hql（当然sql也算）。hibernate还没有其它的方法。先就这样吧。