具有多个 COUNT(DISTINCT xxx) 的 PL/SQL SELECT - 意外结果

Question

我正在尝试对 Oracle 11g 应用程序进行查询，但遇到了问题。

我将简化实际场景，使其更易于理解（同时也保护客户端的数据）：

• 表 A 是基表。它有一个已知的标识符，我将其传递给查询。
• 对于表 A 中的每个条目，表 B 中可能有多个条目。表 B 包含我感兴趣的值。
• 对于表 B 中的每个条目，表 C 中也可能有多个条目。表 C 包含我感兴趣的另一个值。 该
• 我还有一个 XML 片段，其中包含可能与表 C 中感兴趣的值匹配或不匹配的值列表。
• 查询对 XML 进行外部联接，以便如果存在匹配值，它将返回再次该值，否则为空。

我想要做的是取回我传入的标识符、B 和 C 中的唯一值的计数，以及来自连接的 XML 部分的唯一（且非空）值的计数。

我当前的查询是：

SELECT
    a.ID
  , COUNT(DISTINCT b.VAL) AS B_VAL
  , COUNT(DISTINCT c.VAL) AS C_VAL
  , COUNT(DISTINCT xml.VAL) AS XML_VAL
FROM a, b, c,
  XMLTABLE('/field1/collection/value' passing my_xml_type
    COLUMNS VAL VARCHAR2(50) PATH '.') xml
WHERE
      a.ID = b.SOME_ID
  AND b.OTHER_ID = c.OTHER_ID
  AND c.VAL = xml.VAL (+)

现在，如果您忘记计数而只返回行，则示例结果集可能如下所示：

ID     B_VAL     C_VAL     XML_VAL
---------------------------------------
X      abc       123       123
X      abc       456       null
X      abc       789       789
X      abc       789       789

期望：现在，当我想做不同的计数时，我想它返回：

ID     B_VAL     C_VAL     XML_VAL
---------------------------------------
X      1         3         2

ACTUAL: 但是，当我将它们全部设置为 COUNT(DISTINCT ...):

ID     B_VAL     C_VAL     XML_VAL
---------------------------------------
X      1         1         1

ALTERNATIVE: ...并且如果然后我把 DISTINCT 从计数中去掉我得到：

ID     B_VAL     C_VAL     XML_VAL
---------------------------------------
X      1         4         3

为什么 DISTINCT 似乎只在特定的 B_VAL 内运行，但将其取出会导致它在所有行上运行但不考虑唯一性？

是否有另一种方法可以做到这一点，而不必将所有联接复制为子查询？我完全没有抓住要点吗？

（请注意，我根本不是数据库开发人员，我只是被拉进来帮忙，所以很抱歉，如果这是一个简单的问题......我在发布之前已经搜索了谷歌并浏览了这个网站以获取答案，不过！）

谢谢。

---

我发现，如果我取出 XML 表连接，则不同的计数在 B_VAL 和 C_VAL 上工作正常...那么 Oracle 处理 XML 表连接的方式可能有点奇怪？

Answer 1

由于 Vincent 的测试用例适用于 10.2.0.3 和 11.2.0.2，并且如果您使用的是 11g 的早期版本，则这可能是错误 8816675：XMLexists 查询使用 select DISTINCT 返回错误结果。 bug 中的示例涉及 count(distinct) 的问题。您没有明确使用 XMLexists，但该错误可能会产生比标题所暗示的更广泛的影响，或者它可能会在幕后使用。

如果这是问题所在，并且您无法修补，您也许可以通过包装非计数版本来解决它，但这仍然不太漂亮：

SELECT
    A_ID
    , COUNT(DISTINCT B_VAL) AS B_VAL
    , COUNT(DISTINCT C_VAL) AS C_VAL
    , COUNT(DISTINCT XML_VAL) AS XML_VAL
FROM (
SELECT a.ID as A_ID, b.VAL as B_VAL, c.VAL as C_VAL, xml.VAL as XML_VAL
FROM a, b, c
    , XMLTABLE('/field1/collection/value' passing my_xml_type
        COLUMNS VAL VARCHAR2(50) PATH '.') xml
WHERE a.ID = b.SOME_ID
AND b.OTHER_ID = c.OTHER_ID
AND c.VAL = xml.VAL (+)
)
GROUP BY A_ID;

Answer 2

我无法使用 Oracle 10.2.0.3 重现您的发现。

这是我的设置：

SQL> CREATE TABLE a AS SELECT 'X' ID FROM dual;

Table created

SQL> CREATE TABLE b AS SELECT 'abc' val, 'X' some_id, 1 other_id FROM dual;

Table created

SQL> CREATE TABLE c AS
  2     SELECT 1 other_id, '123' val,
  3            XMLTYPE('<field1>
  4                        <collection><value>123</value></collection>
  5                     </field1>') my_xml_type
  6       FROM dual UNION ALL
  7     SELECT 1 other_id, '456' val, NULL FROM dual UNION ALL
  8     SELECT 1 other_id, '789' val,
  9            XMLTYPE('<field1>
 10                        <collection><value>789</value></collection>
 11                        <collection><value>789</value></collection>
 12                     </field1>') my_xml_type
 13       FROM dual;

Table created

查询返回正确的结果：

SQL> SELECT
  2      a.ID
  3    , COUNT(DISTINCT b.VAL) AS B_VAL
  4    , COUNT(DISTINCT c.VAL) AS C_VAL
  5    , COUNT(DISTINCT xml.VAL) AS XML_VAL
  6  FROM a, b, c
  7     , XMLTABLE('/field1/collection/value' passing my_xml_type
  8                 COLUMNS VAL VARCHAR2(50) PATH '.') xml
  9  WHERE a.ID = b.SOME_ID
 10    AND b.OTHER_ID = c.OTHER_ID
 11    AND c.VAL = xml.VAL (+)
 12  GROUP BY a.id;

ID      B_VAL      C_VAL    XML_VAL
-- ---------- ---------- ----------
X           1          3          2

你能运行这个测试用例吗？