问具有多个计数的PL/SQL SELECT (不同xxx) -意外结果
EN

Stack Overflow用户

提问于 2011-06-07 22:01:43

回答 2查看 8.3K关注 0票数 4

我试图为Oracle 11g应用程序编写一个查询，但遇到了一个问题。

我将简化真实场景，以便更容易理解(并保护客户端的数据)：

表A是基表。它有一个已知的标识符，我传递给查询。表A中的每个条目可能有多个
表B中可能有多个条目。表B中的表B包含我感兴趣的值。表B中的每个条目可能有多个条目，表C中也有表C中的多个条目。表C包含我感兴趣的另一个值。
也有一个XML片段，其中包含的值列表可能与表C中感兴趣的值相匹配，也可能与表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

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

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

what :然而，当我将它们全部作为计数时，这就是我所得到的(不同的.)：

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

如果我从计数中取出不同的内容，ALTERNATIVE: ...and就会得到：

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

为什么不同的东西似乎只在一个特定的B_VAL中运行，但是去掉它会导致它跨所有行操作，而没有考虑到唯一性？

还有另一种不需要将所有连接作为子查询进行复制的方法吗？我是否完全忽略了这一点？

(请注意，我根本不是DB开发人员，我只是被拉过来帮忙，所以很抱歉这是个简单的问题.不过，我已经搜索过谷歌并浏览了这个网站的答案，然后才发帖！)

谢谢。

我已经发现，如果我把XML表连接出来，那么计数不同在B_VAL和C_VAL之间可以正常工作.所以，也许Oracle处理XML表联接的方式有些奇怪吗？

distinct

sql

xml

oracle

count

云点播特惠1元起

提供制作上传、存储、转码、媒体处理、媒体 AI、加速分发播放、版权保护等一体化的高品质媒体服务

回答 2

Stack Overflow用户

回答已采纳

发布于 2011-06-08 02:12:27

正如文森特的测试用例在10.2.0.3和11.2.0.2中工作一样，如果您在早期版本的11g中，这可能是bug 8816675: XMLexists查询返回错误的结果和选择不同。bug中的示例是指count(distinct)的一个问题。您没有显式地使用XMLexists，但是这个bug可能会产生比标题建议的更广泛的影响，或者它可能会在引擎盖下面使用。

如果这是问题所在，而且您无法修补，您可能可以通过包装不计数版本来解决这个问题，而这个版本仍然不太漂亮：

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;

票数 4

Stack Overflow用户

发布于 2011-06-08 01:06:55

我无法用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