Senarai lajur dalam Jadual 1: Plan_ID, Claim_id, Patient_id, B_OR_G
Senarai lajur dalam Jadual 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
Dalam pertanyaan di atas, saya ingin mengekstrak satu lagi lajur bernama "B_or_G" daripada jadual1 (iaitu a) tanpa mengganggu kumpulan mengikut klausa kerana ia perlu mengikut keperluan kami.
Adakah cara yang lebih baik untuk melakukan ini? Terima kasih! !
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Saya rasa anda boleh menggunakan ANY_VALUE(B_or_G)
Contohnya:
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