java - 按子查询属性过滤条件查询

Question

我正在尝试实现一个有点复杂的标准。
我的第一个查询是这样的。

SELECT * FROM pin AS activepin
WHERE activepin.pin_nbr NOT IN 
( 
 SELECT p.pin_nbr 
 FROM pin AS p 
 GROUP BY p.pin_nbr,p.is_active 
 HAVING p.is_active = 0 
 AND count(*) =( 
    SELECT count(*) 
    FROM pin 
    WHERE pin_nbr = p.pin_nbr 
    GROUP BY pin_nbr)
 );

所以，为了将having转换为where子句(我认为having和标准不是好 friend xD)，我将其转换为:

SELECT *
FROM pin AS activePin
WHERE activePin.pin_nbr NOT IN (
    SELECT innerQuery2.pin_nbr
    FROM(
        SELECT p.pin_nbr,
               p.is_active,
               count(*) AS quantity
        FROM pin p
        GROUP BY p.pin_nbr,
                 p.is_active) AS innerQuery2
     WHERE innerQuery2.is_active = 0
     AND innerQuery2.quantity =(
         SELECT count(*)
         FROM pin
         WHERE pin_nbr = innerQuery2.pin_nbr
         GROUP BY pin_nbr
         )
 );

然后我做了这个标准

        DetachedCriteria innerQuery3 = DetachedCriteria.forClass(Pin.class, "innerQuery3")
                .setProjection(Projections.rowCount())
                .setProjection(Projections.groupProperty("innerQuery3.pinNbr"))
                .add(Restrictions.eq("innerQuery2.pinNbr", "pinNbr"));

        DetachedCriteria innerQuery2 = DetachedCriteria.forClass(Pin.class, "innerQuery2")
                .setProjection(Projections.rowCount())
                .setProjection(Projections.groupProperty("innerQuery2.pinNbr"))
                .setProjection(Projections.groupProperty("innerQuery2.active"))
                .add(Subqueries.eq(Projections.rowCount(), innerQuery3))
                .add(Restrictions.eq("innerQuery2.pinNbr", "pinNbr"));

        DetachedCriteria innerQuery1 = DetachedCriteria.forClass(Pin.class, "innerQuery1")
                .setProjection(Projections.property("innerQuery2.pinNbr"))
                .add(Subqueries.exists(innerQuery2))
                .add(Restrictions.eq("innerQuery2.active", false));

        criteria.add(Subqueries.propertyNotIn("pinNbr", innerQuery1));

这引发了我以下异常

org.hibernate.MappingException: Unknown entity: null

我认为发生这种情况是因为我通过子查询值添加了限制(我介意，在innerQuery1处我想通过innerQuery2值进行过滤，那是因为我想在group子句之后进行过滤)。

有任何关于如何做到这一点的线索吗？我一直在研究如何按该子查询之外的子查询值进行过滤，但没有结果。

提前致谢。

Answer 1

SELECT p.pin_nbr 
       SUM(CASE WHEN p.is_active = 0 THEN 0 ELSE 1 END) as sum_active
       count(*) as total
FROM pin AS p 
GROUP BY p.pin_nbr
HAVING total!=sum_active

我们首先重写 SQL 查询。据我了解，您需要所有 pin_nbr，其中所有项目都处于 Activity 状态。

您可以将 Projections.sqlProjection() 用于上面的表达式。对于查询，您在 java 端添加 HAVING 检查或将其放在子查询中。