条件,子查询,分组依据以及更多 [英] Criteria, Subqueries, Group By and Having more
问题描述
我有一个与颜色表有关系的产品表
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
.
这篇关于条件,子查询,分组依据以及更多的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!