表 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）中再提取一個(gè)名為“B_or_G”的列，但不會(huì)干擾 group by 子句，因?yàn)楦鶕?jù)我們的要求這是必要的。
有沒有更好的方法來做到這一點(diǎn)？ 謝謝！！
1
0
0
我認(rèn)為您可以使用 ANY_VALUE(B_or_G)
舉個(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
