oracle 在OARCLE SQL查询中为空白/空结果打印零

xxls0lw8 于 2023-08-04 发布在 Oracle

关注(0)|答案(2)|浏览(219)

我想在查询不返回任何记录时显示0。我试过用nvl，但它不起作用。请指示。

create table  temp (id number , cat varchar2(10));
insert into temp values (100,'EUR');
insert into temp values (101,'MAR');
insert into temp values (102,'AST');
insert into temp values (103,'GHK');
insert into temp values (104,'MST');
insert into temp values (105,'LAL');

select TOTAL_CNT , COUNT(*) 
FROM
(
SELECT 'T' TOTAL_CNT, nvl(count(DISTINCT id),0) cnt FROM TEMP GROUP BY CAT
union all
SELECT  'U' UNIQUE_CNT ,nvl(count(DISTINCT id),0) cnt FROM TEMP GROUP BY CAT HAVING  COUNT(DISTINCT ID)>1
) GROUP BY TOTAL_CNT;

字符串
预期产出：我没有得到“U”值。

TOTAL_CNT    COUNT(*) 
       T          6
       U          0

型

oracle

来源：https://stackoverflow.com/questions/76626331/print-zero-for-blank-empty-result-in-oarcle-sql-query

2条答案

按热度按时间

lyr7nygr1#

如果你想把值作为列（而不是行），那么你只需要从表中SELECT一次并聚合两次（使用条件聚合来找到U值）：

SELECT COUNT(COUNT(DISTINCT id)) AS t,
       COUNT(CASE WHEN COUNT(DISTINCT id) > 1 THEN 1 END) AS u
FROM   TEMP
GROUP BY CAT;

字符串
哪些输出：
| U| U |
| --| ------------ |
| 0个| 0 |
如果你确实想把它作为行，那么你可以使用UNPIVOT（同样，它不使用UNION，只从表中使用SELECT一次）：

SELECT *
FROM   (
  SELECT COUNT(COUNT(DISTINCT id)) AS t,
         COUNT(CASE WHEN COUNT(DISTINCT id) > 1 THEN 1 END) AS u
  FROM   TEMP
  GROUP BY CAT
)
UNPIVOT (cnt FOR total_cnt IN (t, u));

型
哪些输出：
| CNT| CNT |
| --| ------------ |
| 六个| 6 |
| 0个| 0 |
fiddle

赞(0）回复(0）举报 2023-08-04

q5iwbnjs2#

这是一个选项：

SQL> with
  2  t_unique as
  3    (select count(distinct id) cnt
  4     from temp
  5     group by cat
  6     having count(distinct id) > 1
  7    ),
  8  t_total as
  9    (select count(distinct id) cnt
 10     from temp
 11     group by cat
 12    )
 13  select 'T' what, (select count(*) from t_total ) cnt from dual
 14  union
 15  select 'U' what, (select count(*) from t_unique) cnt from dual;

WHAT         CNT
----- ----------
T              6
U              0

SQL>

字符串

赞(0）回复(0）举报 2023-08-04

我来回答

oracle 在OARCLE SQL查询中为空白/空结果打印零

2条答案

相关问题

热门标签

最新问答