如何用Java Criteria Builder改写无嵌套子查询的客户最新售价SQL

现有两张表，结构如下：

invoice
    id
    customer_id
    create_date
    billing_type

invoice_detail
    invoice_id
    product_detail_id
    quantity
    gross_total

需求：查询指定product_detail_id对应的特定客户（示例customer_id=20465）最新销售单中，gross_total与quantity的比值（即商品当前售价）。需将现有带嵌套子查询的SQL改写为无嵌套子查询的Java Criteria Builder实现。

现有SQL语句：

select ivd.product_detail_id, iv.customer_id, ivd.gross_total / ivd.quantity from (
select * from (
    select
        id,
        create_date,
        customer_id,
        row_number() over(partition by customer_id order by create_date desc) as rn
    from
        invoice
    where billing_type = 'SELLING'
    and customer_id = 20465--参数
) t
where t.rn = 1
) iv
left join invoice_detail ivd on iv.id = ivd.invoice_id
where 
ivd.product_detail_id  in (1,2,3, 56569, 56571)

核心想法是直接用窗口函数在主查询里筛选出客户的最新销售单，通过关联表的方式替代嵌套子查询，逻辑更直接。

假设对应实体类如下：

• Invoice：映射invoice表，包含字段id, customerId, createDate, billingType
• InvoiceDetail：映射invoice_detail表，包含字段invoiceId, productDetailId, quantity, grossTotal，并通过@ManyToOne关联Invoice实体

代码实现：

import jakarta.persistence.criteria.*;
import java.math.BigDecimal;
import java.util.List;

public List<Object[]> getLatestProductPriceForCustomer(CriteriaBuilder cb, EntityManager em, Long customerId, List<Long> productDetailIds) {
    // 初始化主查询，返回数组存储结果字段
    CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
    Root<InvoiceDetail> detailRoot = query.from(InvoiceDetail.class);
    // 关联销售单表，因为要找对应销售单的明细，用INNER JOIN足够
    Join<InvoiceDetail, Invoice> invoiceJoin = detailRoot.join("invoice", JoinType.INNER);

    // 构建窗口函数：按客户ID分组，销售单创建时间倒序排列，生成行号
    Window<Long> window = cb.from(Invoice.class)
            .partitionBy(invoiceJoin.get("customerId"))
            .orderBy(cb.desc(invoiceJoin.get("createDate")));
    Expression<Long> rowNum = cb.rowNumber().over(window);

    // 组装过滤条件
    Predicate[] predicates = {
            cb.equal(invoiceJoin.get("customerId"), customerId),
            cb.equal(invoiceJoin.get("billingType"), "SELLING"),
            cb.equal(rowNum, 1L), // 只取最新的那笔销售单
            detailRoot.get("productDetailId").in(productDetailIds)
    };

    // 计算单商品售价，转成BigDecimal避免精度丢失
    Expression<BigDecimal> unitPrice = cb.divide(
            detailRoot.get("grossTotal").as(BigDecimal.class),
            detailRoot.get("quantity").as(BigDecimal.class)
    );

    // 指定查询返回的字段
    query.multiselect(
            detailRoot.get("productDetailId"),
            invoiceJoin.get("customerId"),
            unitPrice
    ).where(predicates);

    return em.createQuery(query).getResultList();
}

代码说明 #

• 直接用Join关联明细和销售单表，完全避免嵌套子查询
• 窗口函数rowNumber()按客户分组、创建时间倒序，直接筛选出该客户的最新销售单（行号=1）
• 用cb.divide()计算售价时转成BigDecimal，防止整数除法导致的精度丢失
• 所有过滤条件直接拼在主查询的where中，逻辑清晰易懂

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