如果访问一个位置，则只需选择

debugcn 发表于 Dev

布达佩斯脂肪

在下面的SQL中，它只查看location_ID = 5中的那些凭单。我该如何编码，我只希望从仅访问location_ID 5的Patient_ID中获取？

SELECT "Vouchers"."Patient_ID", "vwGenPatInfo"."Patient_Number",  
       "Practitioners"."Practitioner_ID", "Practitioners"."First_Name",   
       "Practitioners"."Last_Name", "vwGenPatInfo"."Patient_First_Name", 
       "vwGenPatInfo"."Patient_Last_Name", "vwGenPatInfo"."Patient_DOB", 
       "vwGenPatInfo"."Patient_Sex", "Vouchers"."Carrier_ID", 
       "Vouchers"."Billing_Date", "Vouchers"."Patient_Policy_ID", 
       "Vouchers"."Location_ID"
FROM   ("Ntier_70751"."PM"."vwGenPatInfo" "vwGenPatInfo" 
INNER JOIN "Ntier_70751"."PM"."Vouchers" "Vouchers" 
ON "vwGenPatInfo"."Account_ID"="Vouchers"."Account_ID") 
INNER JOIN "Ntier_70751"."PM"."Practitioners" "Practitioners" 
ON "Vouchers"."Actual_Prov_Practitioner_ID"="Practitioners"."Practitioner_ID"
-- 
WHERE  "Vouchers"."Location_ID"=5

肖恩·兰格

这是执行此操作的一种方法。我还摆脱了所有那些不必要的双引号，并使用了适当的别名。

SELECT V.Patient_ID
    , gpi.Patient_Number
    , P.Practitioner_ID
    , P.First_Name
    , P.Last_Name
    , gpi.Patient_First_Name
    , gpi.Patient_Last_Name
    , gpi.Patient_DOB
    , gpi.Patient_Sex
    , V.Carrier_ID
    , V.Billing_Date
    , V.Patient_Policy_ID
    , V.Location_ID
FROM Ntier_70751.PM.vwGenPatInfo gpi 
INNER JOIN Ntier_70751.PM.Vouchers V ON gpi.Account_ID = V.Account_ID 
INNER JOIN Ntier_70751.PM.Practitioners P ON V.Actual_Prov_Practitioner_ID = P.Practitioner_ID
cross apply
(
    select V2.Account_ID
    from Ntier_70751.PM.Vouchers V2
    where V2.Account_ID = V.Account_ID

    group by V2.Account_ID
    HAVING MAX(Location_ID) = 5
        AND MIN(Location_ID) = 5
) x

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。