条件，子查询，分组依据以及更多

本文介绍了条件，子查询，分组依据以及更多的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个与颜色表有关系的产品表

I have a product table with a relationship to color table

一个产品可以有很多颜色... exp：产品A：具有红色，绿色，蓝色，黄色。

a product can have many colors... exp: Product A: has red, green blue yellow.

我希望找到至少包含红色和绿色的产品。

I wish to find the product which contain at least RED and GREEN.

DetachedCriteria colorCrit = DetachedCriteria.forClass(Color.class); ProjectionList colorList = new Projections.projectionList(); colorList.add(Projections.groupProperty("productID")); colorList.add(Projections.rowCount(),"abc"); colorCrit.setProjection(colorList); colorCrit.add(Restrictions.eq("color", "GREEN") colorCrit.add(Restrictions.eq("color", "RED") colorCrit.add(Restrictions.eq("abc",2); Criteria productCrit = new Criteria(Product.class); productCrit.add(Suqueries.in("id",colorCrit)); list<Product> productList = productCrit.list();

我使用上面的代码，但是我无法通过使用 Projections.rowCount（）来达到目标​​。

I use the above code, but I am unable to achieve the group by having on Projections.rowCount().

我已经尝试过.as，但是它将导致另外一列，使分离的条件不适用于Suqueries（值太多的oracle异常）

I have tried .as but it will result in an additional column which make the detached criteria not suitable Suqueries. (Too many value oracle exception)

colorCrit.add（Restrictions.eq（Projections.rowCount（），2）; >不起作用，因为rowcount不是属性= x

colorCrit.add(Restrictions.eq(Projections.rowCount(),2); > does not work because rowcount is not a property = x

select * from product pd where pd.id = (select cr.productID from color cr where cr.color="RED" or cr.color="GREEN" group by cr.productID having rowcount=2

上面应该是正确的SQL查询。

The above should be the proper SQL query.

我可以知道有解决方案吗？

May I know is there a solution?

推荐答案

我将使用以下查询：

select p from Product p where 2 = (select count(color.id) from Product p2 inner join p2.colors color where p2.id = p.id and color.color in ('GREEN', 'RED'))

上面的标准可以翻译为

Criteria c = session.createCriteria(Product.class, "p") DetachedCriteria sub = DetachedCriteria.forClass(Product.class, "p2"); sub.createAlias("p2.colors", "color"); sub.add(Restrictions.eqProperty("p2.id", "p.id")) sub.add(Restrictions.in("color.color", new String[] {"RED", "GREEN"})); sub.setProjection(Projections.count("color.id")); c.add(Subqueries.eq(2, sub)); // or 2L

以上假设您不能购买红色为2种颜色的产品，即元组（color，product_id）在表 color 中具有唯一约束。

The above assumes that you can't have a product which has 2 colors being RED, i.e. that the tuple (color, product_id) has a unique constraint in the table color.