我有這樣的查詢來創建日期系列：
Select month
From
(select to_char(created_date, 'Mon') as Month,
created_date::date as start_day,
(created_date::date interval '1 month - 1 day ')::date as end_day
from generate_series(date '2021-01-26',
date '2022-04-26', interval '1 month') as g(created_date)) AS "thang"
表格如下所示：
| 月 |
|---|
| 簡 |
| 二月 |
| 三月 |
| 四月 |
| 可能 |
| 君 |
| 七月 |
| 八月 |
| 九月 |
| 十月 |
現在我想status從KYC表中數數。
所以我試試這個：
Select
(Select month
From
(select to_char(created_date, 'Mon') as Month,
created_date::date as start_day,
(created_date::date interval '1 month - 1 day ')::date as end_day
from generate_series(date '2021-01-26',
date '2022-04-26', interval '1 month') as g(created_date)) AS "thang"),
count(*) filter (where status = 4) as "KYC_Success"
From kyc
group by 1
我希望結果是這樣的：
Month | KYC_Success
Jan | 234
Feb | 435
Mar | 546
Apr | 157
但它說
錯誤：用作運算式的子查詢回傳多行
我應該在這個查詢中改變什么？
uj5u.com熱心網友回復：
讓我們假設該表KYC有一個名為的時間戳列created_date和一個狀態列，并且您希望每月計算成功狀態 - 即使一個月內成功專案為零。
SELECT thang.month
, count(CASE WHEN kyc.STATUS = 'success' THEN 1 END) AS successes
FROM (
SELECT to_char(created_date, 'Mon') AS Month
, created_date::DATE AS start_date
, (created_date::DATE interval '1 month - 1 day ')::DATE AS end_date
FROM generate_series(DATE '2021-01-26', DATE '2022-04-26', interval '1 month') AS g(created_date)
) AS "thang"
LEFT JOIN kyc ON kyc.created_date>= thang.start_date
AND kyc.created_date < thang.end_date
GROUP BY thang.month;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/437318.html
標籤：PostgreSQL 子查询