如何在保留 group by 子句的同時在同一個表中再新增一列

Question

表 1 中的欄位清單： Plan_ID、Claim_id、Patient_id、B_OR_G

#表2中的欄位清單： 奧吉德、沙普蘭德

select distinct a.Plan_ID
       , a.Total_Claims
       , Total_Patients
       ,  b.PERIOD
       , b.ORGID,a.B_OR_G
FROM (Select distinct Plan_ID
             , count(distinct Claim_id) as Total_Claims
             , count(distinct Patient_id)  as Total_Patients 
      from table1 group by 1) a
JOIN (select *
             , row_number() over (partition by ORGID,SHAPLANID order by PROCESSINGDATE desc) as rank
      from table2 qualify rank = 1) b
ON LTRIM(a.PLAN_ID, '0') = b.SHAPLANID

在上面的查詢中，我想從 table1（即 a）中再提取一個名為「B_or_G」的列，但不會幹擾 group by 子句，因為根據我們的要求這是必要的。

有沒有更好的方法來做到這一點？ 謝謝！ ！

Answer 1

我認為您可以使用 ANY_VALUE(B_or_G)

舉個例子：

select distinct a.Plan_ID
       , a.Total_Claims
       , Total_Patients
       ,  b.PERIOD
       , b.ORGID,a.B_OR_G
FROM (Select distinct Plan_ID
             , count(distinct Claim_id) as Total_Claims
             , count(distinct Patient_id)  as Total_Patients 
             , ANY_VALUE(B_OR_G)
      from table1 group by 1) a
JOIN (select *
             , row_number() over (partition by ORGID,SHAPLANID order by PROCESSINGDATE desc) as rank
      from table2 qualify rank = 1) b
ON LTRIM(a.PLAN_ID, '0') = b.SHAPLANID