子查询在两个不同的时间列中

Question

我尝试过这样做，但是它说错误:子查询必须只返回一列

 Select date_trunc('week', kyc.kyc_verify_date::timestamptz) as "week",
        COUNT(*) filter (where kyc.status = 4) AS "A1",
        COUNT(CASE WHEN kyc.status = 5 
              THEN 
                   (Select date_trunc('week', kyc.last_update_time::timestamptz) AS "week",
                        count(*) filter (where kyc.status = 5)
                    From kyc
                    Group by 1)
              END) AS "A2"
from KYC 
Where kyc.kyc_verify_date >= date_trunc('week', CURRENT_TIMESTAMP - interval '4 week') 
       and kyc.kyc_verify_date < date_trunc('week', CURRENT_TIMESTAMP)
Group by 1

我做这个查询是为了得到这样的结果:如果status = 4将取日期为kyc_verify_date，

但是如果status = 5愿意的话，日期是last_update_time

在这个查询中我应该更改什么？还是有更好的办法？

Answer 1

case表达式返回单个值的。因为您需要它的两个值，所以需要重复这个表达式。