Oracle如何从(子查询)简化选择计数(*)？

我被要求尝试简化count()查询,但我不知道从哪里开始,查询是这样的:

SELECT COUNT( 1 )
FROM (
        SELECT DISTINCT a.col,b.colx,c.coly
        FROM  a
            JOIN b on a.id = b.id
            JOIN c on b.id = c.id
        WHERE a.xyz = 'something'
        AND   b.hijk = 'something else'
        AND c.id IN (
                SELECT cid
                FROM cwa
                WHERE csid = 22921
            )
        ORDER BY
            e.create_timestamp DESC
    );

我被告知SELECT COUNT(1) FROM (subquery) 可以简化,怎么办呢？

我尝试了几件事,但结果与上面的查询不同.

一个order-by子查询不,除非你在过滤ROWNUM结果有用的(有时会出错,具体情况决定).您可以使用连接替换内部子查询:

SELECT COUNT(*)
FROM (
    SELECT DISTINCT a.col,b.colx,c.coly
    FROM  a
    JOIN b on a.id = b.id
    JOIN c on b.id = c.id
    JOIN cwa on c.id cwa.cid
    WHERE a.xyz = 'something'
    AND   b.hijk = 'something else'
    AND   cwa.csid = 22921
);

您甚至可以在没有子查询的情况下执行此操作,如果您可以识别出未在您选择的三个列中的任何一个中出现的字符,那么您可以将其用作分隔符; 例如,如果你永远不会有波浪形你可以做:

SELECT COUNT(DISTINCT a.col ||'~'|| b.colx ||'~'|| c.coly)
FROM  a
JOIN b on a.id = b.id
JOIN c on b.id = c.id
JOIN cwa on c.id cwa.cid
WHERE a.xyz = 'something'
AND   b.hijk = 'something else'
AND   cwa.csid = 22921;

虽然这更简单或更清楚是一个意见问题.

由于count()只接受一个参数,并且您想要计算这三列的(不同)组合,因此该机制将所有三个连接成一个字符串,然后计算该字符串的外观.添加分隔符,以便您可以区分不明确的列值,例如CTE中的人为示例:

with cte (col1, col2) as (
  select 'The', 'search' from dual
  union all select 'These', 'arch' from dual
)
select col1, col2,
  col1 || col2 as bad,
  col1 ||'~'|| col2 as good
from cte;

COL1  COL2   BAD         GOOD        
----- ------ ----------- ------------
The   search Thesearch   The~search  
These arch   Thesearch   These~arch  

通过简单的"坏"连接,两行看起来都是一样的; 通过添加分隔符来创建"好"版本,您仍然可以区分它们,因此计算不同的连接值会得到正确的答案:

with cte (col1, col2) as (
  select 'The', 'search' from dual
  union all select 'These', 'arch' from dual
)
select count(distinct col1 || col2) as bad_count,
  count (distinct col1 ||'~'|| col2) as good_count
from cte;

 BAD_COUNT GOOD_COUNT
---------- ----------
         1          2

如果col1以波形符号结束,或col2以波形符号开头,您将回到模糊性:

with cte (col1, col2) as (
  select 'The~', 'search' from dual
  union all select 'The', '~search' from dual
)
select col1, col2,
  col1 || col2 as bad,
  col1 ||'~'|| col2 as still_bad
from cte;

COL1 COL2    BAD         STILL_BAD   
---- ------- ----------- ------------
The~ search  The~search  The~~search 
The  ~search The~search  The~~search 

所以分隔符必须是你在任何值中都找不到的东西.