如何在GROUP BY查询中检查关联的OrderLineRealizations记录是否存在？

需求 #

从Orders、OrderLines、OrderLineRealizations三个关联表中查询订单信息，返回：

• 订单ID
• 该订单下所有订单行的数量总和（TotalAmount）
• 标识该订单是否存在任意订单行的实现记录（RealizationExists，1为存在，0为不存在）

初始查询框架 #

用户提供的基础查询结构：

SELECT 
Orders.Id,
SUM(OrderLines.Quantity) AS TotalAmount,
-- 需添加RealizationExists列
FROM Orders
INNER JOIN OrderLines ON OrderId = Orders.Id
GROUP BY Orders.Id

用户尝试的问题 #

用户尝试用EXISTS子查询判断，但因IN (OrderLines.Id)的写法不符合语法规则无法运行；直接左联OrderLineRealizations会导致订单行数量被重复计算，TotalAmount结果错误：

SELECT 
Orders.Id,
SUM(OrderLines.Quantity) AS TotalAmount,
CASE
    WHEN EXISTS (SELECT * FROM OrderLineRealizations
                 WHERE OrderLineId IN (OrderLines.Id))
    THEN 1
    ELSE 0
END AS RealizationExists
FROM Orders
INNER JOIN OrderLines ON OrderId = Orders.Id
GROUP BY Orders.Id

示例数据 #

Orders表 #

OrderLines表 #

OrderLineRealizations表 #

预期结果 #

可行解决方案 #

方案1：子查询预筛选有实现记录的订单ID #

先通过子查询找出所有存在实现记录的订单ID并去重，再关联主查询，避免数量重复计算：

SELECT 
    o.Id AS OrderId,
    SUM(ol.Quantity) AS TotalAmount,
    CASE WHEN orr.OrderId IS NOT NULL THEN 1 ELSE 0 END AS RealizationExists
FROM Orders o
INNER JOIN OrderLines ol ON ol.OrderId = o.Id
LEFT JOIN (
    SELECT DISTINCT ol_inner.OrderId
    FROM OrderLines ol_inner
    INNER JOIN OrderLineRealizations olr ON olr.OrderLineId = ol_inner.Id
) orr ON orr.OrderId = o.Id
GROUP BY o.Id, orr.OrderId

方案2：使用EXISTS关联订单ID判断 #

在CASE语句中通过EXISTS直接判断当前订单是否存在实现记录，逻辑清晰且性能较好：

SELECT 
    o.Id AS OrderId,
    SUM(ol.Quantity) AS TotalAmount,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM OrderLineRealizations olr
            INNER JOIN OrderLines ol_inner ON ol_inner.Id = olr.OrderLineId
            WHERE ol_inner.OrderId = o.Id
        ) THEN 1 
        ELSE 0 
    END AS RealizationExists
FROM Orders o
INNER JOIN OrderLines ol ON ol.OrderId = o.Id
GROUP BY o.Id

方案3：左联合并后用MAX函数聚合 #

先对OrderLineRealizations按订单行ID去重，再左联订单行，最后用MAX函数判断是否存在实现记录，确保数量计算准确：

SELECT 
    o.Id AS OrderId,
    SUM(ol.Quantity) AS TotalAmount,
    MAX(CASE WHEN olr_distinct.OrderLineId IS NOT NULL THEN 1 ELSE 0 END) AS RealizationExists
FROM Orders o
INNER JOIN OrderLines ol ON ol.OrderId = o.Id
LEFT JOIN (
    SELECT DISTINCT OrderLineId 
    FROM OrderLineRealizations
) olr_distinct ON olr_distinct.OrderLineId = ol.Id
GROUP BY o.Id

说明 #

• 方案2的EXISTS逻辑在大多数数据库中会提前终止匹配查询，性能更优
• 若OrderLineRealizations中一个订单行对应多条记录，方案3必须添加去重逻辑，否则会导致Quantity被重复累加

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