MSSQL多列WHERE IN子句兼容问题（Hibernate Criteria生成）及替代方案咨询

Ah, right—MSSQL doesn't support that PostgreSQL-style row constructor syntax ((col1, col2) IN (subquery)), which is why your query fails there. No worries though, there are a few cross-database compatible alternatives that work for both PostgreSQL and MSSQL, and can be implemented with Hibernate's Criteria API too.

Recommended: Use EXISTS Subquery #

This is the most reliable and performant approach, as it's supported by all major databases and avoids potential duplication issues from joins. The logic matches your original query: for each row in the main table, we check if there's a corresponding group (by id) where the max a equals the row's a, and the group's b equals the row's b.

SQL Example #

SELECT * 
FROM table t 
WHERE EXISTS (
    SELECT 1 
    FROM table this_ 
    GROUP BY this_.id 
    HAVING MAX(this_.a) = t.a AND this_.b = t.b
)

Hibernate Criteria Implementation #

CriteriaBuilder cb = session.getCriteriaBuilder();
CriteriaQuery<YourEntity> cq = cb.createQuery(YourEntity.class);
Root<YourEntity> root = cq.from(YourEntity.class);

// Build the EXISTS subquery
Subquery<Integer> subquery = cq.subquery(Integer.class);
Root<YourEntity> subRoot = subquery.from(YourEntity.class);
subquery.select(cb.literal(1)) // Select a dummy value, we only care about existence
        .groupBy(subRoot.get("id"))
        .having(cb.and(
                cb.equal(cb.max(subRoot.get("a")), root.get("a")),
                cb.equal(subRoot.get("b"), root.get("b"))
        ));

// Add the EXISTS condition to the main query
cq.where(cb.exists(subquery));

List<YourEntity> result = session.createQuery(cq).getResultList();

Alternative: Use a JOIN with Subquery #

If you prefer joins over subqueries, you can join the main table with the aggregated subquery results. Just remember to use DISTINCT to avoid duplicate rows if multiple groups match the same (a,b) pair.

SQL Example #

SELECT DISTINCT t.* 
FROM table t
INNER JOIN (
    SELECT MAX(this_.a) AS max_a, this_.b AS group_b, this_.id 
    FROM table this_ 
    GROUP BY this_.id, this_.b
) sub ON t.a = sub.max_a AND t.b = sub.group_b

Hibernate Criteria Implementation #

CriteriaBuilder cb = session.getCriteriaBuilder();
CriteriaQuery<YourEntity> cq = cb.createQuery(YourEntity.class);
Root<YourEntity> root = cq.from(YourEntity.class);

// Build the aggregated subquery
Subquery<Tuple> subquery = cq.subquery(Tuple.class);
Root<YourEntity> subRoot = subquery.from(YourEntity.class);
subquery.select(cb.tuple(
        cb.max(subRoot.get("a")).alias("max_a"),
        subRoot.get("b").alias("group_b")
))
.groupBy(subRoot.get("id"), subRoot.get("b"));

// Join main table with subquery results
root.join(
        "dummy", // Dummy association name (Hibernate requires a name here)
        JoinType.INNER,
        cb.and(
                cb.equal(root.get("a"), cb.function("max", Integer.class, subRoot.get("a"))),
                cb.equal(root.get("b"), subRoot.get("b"))
        )
);

// Add DISTINCT to avoid duplicates
cq.select(root).distinct(true);

List<YourEntity> result = session.createQuery(cq).getResultList();

Not Recommended: String Concatenation (Use Only If Necessary) #

You could concatenate a and b into a single string and use a standard IN clause, but this has major caveats: null values will break matches, and if a or b contains your chosen separator, you'll get false matches. Only use this if you're certain your data doesn't have these edge cases.

SQL Example #

SELECT * 
FROM table t 
WHERE CONCAT(t.a, '|', t.b) IN (
    SELECT CONCAT(MAX(this_.a), '|', this_.b) 
    FROM table this_ 
    GROUP BY this_.id, this_.b
)

内容的提问来源于stack exchange，提问作者Noshaf