So fügen Sie derselben Tabelle eine weitere Spalte hinzu und behalten dabei die Group-By-Klausel bei

Question

Spaltenliste in Tabelle 1: Plan_ID, Anspruchs-ID, Patienten-ID, B_OR_G

Spaltenliste in Tabelle 2: Ojid, Shapland

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

In der obigen Abfrage möchte ich eine weitere Spalte mit dem Namen „B_or_G“ aus Tabelle1 (d. h. a) extrahieren, ohne die Group-By-Klausel zu beeinträchtigen, da dies gemäß unserer Anforderung erforderlich ist.

Gibt es einen besseren Weg, dies zu tun? Danke! !

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