代码之家 › 专栏 › 技术社区 › Turab

即使没有匹配项,如何选择连接的行?

firebird join sql

1

Turab · 技术社区 · 8 年前

这是我运行的查询;

SELECT
  C.NAME, C.GROUPNAME, C.EMAIL,
  COALESCE(COUNT(DISTINCT S.ORDERNO), '0') AS TOTALORDERS,
  COALESCE(SUM(S.AMOUNT), '0') as TOTALREVENUE
FROM CUSTOMERS C
LEFT OUTER JOIN STOCK_ACTIVITY S ON C.ID = S.CUSTOMERID
WHERE C.GROUPNAME = 'B'
  AND (S.TYPE = 'RECEIPT' OR S.TYPE = 'INVOICE')
GROUP BY C.NAME, C.GROUPNAME, C.EMAIL

没有连接,我得到了570行(客户),这是正确的结果集。当我加入订单表以获取这些客户的总订单金额时;我只得到379个结果;哪些是至少有一个订单的。这意味着没有订单的客户不会退货。正如你可能已经猜到的那样;我想让活动为零的客户返回“0”作为订单金额和收入。

3 回复 | 直到 8 年前

1

4

Mark Adelsberger 8 年前

问题是你的 WHERE 子句过滤“右侧”表的值。

WHERE ...
  AND (S.TYPE = 'RECEIPT' OR S.TYPE = 'INVOICE')

NULL 右表中所有列的值。所以 S.TYPE 无效的 为了那些记录。

有两种可能的解决方案:

明确允许“ 记录“案例” 哪里

根据某些标准,在将连接条件与过滤器分离时,这可能“更纯粹”,但它可能变得相当复杂(因此容易出错)。需要注意的一个问题是,您可能需要区分生成的 无效的 右表的“真实”记录中恰好有一些 无效的 数据

应该相当安全。您可以测试正确表的PK值是否为 无效的 (假设您在该表上有一个真实的PK)。

外连接的子句 ON

这很简单,看起来像

SELECT C.NAME, C.GROUPNAME, C.EMAIL,
       COALESCE(COUNT(DISTINCT S.ORDERNO), '0') AS TOTALORDERS,
       COALESCE(SUM(S.AMOUNT), '0') as TOTALREVENUE
  FROM                 CUSTOMERS C
       LEFT OUTER JOIN STOCK_ACTIVITY S
                    ON C.ID = S.CUSTOMERID
                   AND (S.TYPE = 'RECEIPT' OR S.TYPE = 'INVOICE')
 WHERE C.GROUPNAME = 'B'
 GROUP BY C.NAME, C.GROUPNAME, C.EMAIL

这有效地过滤了 STOCK_ACTIVITY CUSTOMERS 记录(指仍然可以在没有干扰的情况下生成记录)。(“有效”,因为像你知道DBMS将遵循什么步骤那样说话是愚蠢的;我们所能说的是,这与你通过遵循某些步骤得到的效果相同……)

2

0

Tim Schmidt 8 年前

STOCK_ACTIVITY 对于 CUSTOMER 一行充满了 NULL WHERE AND (S.TYPE = 'RECEIPT' OR S.TYPE = 'INVOICE') 这些台词永远不会是真的。

3

-1

Ádám Géczi 8 年前

保持聚合操作与联接分离。这是最干净的。首先进行分组,然后加入附加信息。