如何在OracleSQL查詢中獲取COUNT=0的行？

我有一個存盤檔案資訊的 Oracle SQL 表。該表有 3 列（ID、creationDate、status）。只有 2 個狀態（“STATUS1”、“STATUS2”）

對于每個可用的日期和狀態，我希望獲得具有這些特征的檔案總數（包括總計數 = 0 時）。

我的代碼如下：

SELECT DISTINCT TO_CHAR(D.CREATED_ON, 'DD-MM-YY') AS DATE, D.STATUS, COUNT(*) AS TOTAL
FROM DOCUMENTS D
GROUP BY DISTINCT TO_CHAR(D.CREATED_ON, 'DD-MM-YY'), D.STATUS
ORDER BY 1 ASC;

回傳：

| DATE      | STATUS | TOTAL |
|-----------|--------|-------|
| 14-01-22  | STATUS1| 2     |
| 14-01-22  | STATUS2| 1     |
| 15-01-22  | STATUS2| 3     |
| 16-01-22  | STATUS1| 2     |

我希望它回傳：

| DATE     | STATUS | TOTAL |
|----------|--------|-------|
| 14-01-22 |STATUS1 | 2     |
| 14-01-22 |STATUS2 | 1     |
| 15-01-22 |STATUS1 | 0     | <--
| 15-01-22 |STATUS2 | 3     |
| 16-01-22 |STATUS1 | 2     |
| 16-01-22 |STATUS2 | 0     | <--

這可能嗎？

uj5u.com熱心網友回復：

你需要一些幫助——正如 HoneyBadger 評論的那樣，一種日歷。其中兩個，實際上，命名所有狀態和所有涉及的日期。

有樣本資料

SQL> with documents (id, created_on, status) as
  2    (select 1, date '2022-01-14', 'STATUS1' from dual union all
  3     select 2, date '2022-01-14', 'STATUS1' from dual union all
  4     select 3, date '2022-01-14', 'STATUS2' from dual union all
  5     select 4, date '2022-01-15', 'STATUS2' from dual union all
  6     select 5, date '2022-01-15', 'STATUS2' from dual union all
  7     select 6, date '2022-01-15', 'STATUS2' from dual union all
  8     select 7, date '2022-01-16', 'STATUS1' from dual union all
  9     select 8, date '2022-01-16', 'STATUS1' from dual
 10    ),

和幫助，

 11  all_statuses as
 12    (select distinct status
 13     from documents
 14    ),
 15  all_dates as
 16    (select distinct created_on
 17     from documents
 18    )
 19  select s.created_on, a.status, count(d.id) total
 20  from all_statuses a cross join all_dates s
 21       left join documents d on d.status = a.status and d.created_on = s.created_on
 22  group by s.created_on, a.status
 23  order by s.created_on, a.status;

結果是

CREATED_ON STATUS       TOTAL
---------- ------- ----------
14-01-2022 STATUS1          2
14-01-2022 STATUS2          1
15-01-2022 STATUS1          0
15-01-2022 STATUS2          3
16-01-2022 STATUS1          2
16-01-2022 STATUS2          0

6 rows selected.

SQL>

uj5u.com熱心網友回復：

如果您知道不同狀態的數量，但不知道日期，則可以使用磁區外連接，這將為您生成所有其他狀態：

create table t
as
select
  date '2022-01-01'
    trunc(level/3) as dt
  , case
      when dbms_random.value() > 0.5
      then 'STATUS1'
      else 'STATUS2'
  end as status
from dual
connect by level < 15

with s(status) as (
  /*All possible statuses*/
  select 'STATUS1' from dual
  union all
  select 'STATUS2' from dual
)
, agg as (
  select
    dt, status
    , count(1) as cnt
  from t
  group by
    dt, status
)
select
  dt, status
  , nvl(cnt, 0) as cnt
from agg
  partition by (dt)
  right join s
    using (status)

DT | 狀態 | 碳納米管
:-------- | :-------- | --:
22 年 1 月 1 日 | 狀態1 | 1
22 年 1 月 1 日 | 狀態2 | 1
22 年 1 月 2 日 | 狀態1 | 2
22 年 1 月 2 日 | 狀態2 | 1
22 年 1 月 3 日 | 狀態1 | 3
22 年 1 月 3 日 | 狀態2 | 0
22 年 1 月 4 日 | 狀態1 | 1
22 年 1 月 4 日 | 狀態2 | 2
22 年 1 月 5 日 | 狀態1 | 2
22 年 1 月 5 日 | 狀態2 | 1

db<>在這里擺弄

標籤：

上一篇：在Oracle中根據列的長度生成序列

下一篇：決議PLSQL中沒有鍵的JSON串列