有更好的方法在哪里做这件事吗

Question

我的SQL有点生疏，只是想知道是否有更好的方法来执行WHERE子句？假设有一种方法，我相信有。它运行得很快，但我只是不喜欢它，想知道它是否可以改进。

SELECT DISTINCT p.firstname              ,
            p.middlename             ,
            p.lastname               ,
            p.gender                 ,
            p.dob                    ,
            p.id      AS patientid   ,
            pr.id     AS practiceid  ,
            pr.[name] AS practicename,
            pr.parentaco             ,
            pp.encounterdate
FROM            ((aco.patients_practices patients_practices
            LEFT OUTER JOIN aco.patients p
            ON              (
                             patients_practices.patientid = p.id
                            )
            )
            LEFT OUTER JOIN aco.practices pr
            ON              (
                             patients_practices.practiceid = pr.id
                            )
            )
            INNER JOIN aco.patientpreferences pp
            ON              (
                             pp.patientid = p.id
                            )
WHERE           (
                            pr.parentaco =
                            (SELECT parentaco
                            FROM    aco.practices
                            WHERE   master_companyid = 763
                            )
            OR              pr.id =
                            (SELECT parentaco
                            FROM    aco.practices
                            WHERE   master_companyid = 763
                            )
            )
AND             pp.encounterdate IS NOT NULL

Answer 1

您可以先从子查询中获取值，然后将其放入变量中。

DECLARE @P INT
SELECT @P = parentaco
FROM aco.practices
WHERE master_companyid = 763

在查询中使用变量

WHERE (pr.parentaco = @P or pr.id = @P) AND
      pp.encounterdate IS NOT NULL

Answer 2

由于WHERE子句中引用LEFT OUTER JOIN s的方式，它们被有效地转换为INNER JOIN s。我想你会发现这在逻辑上是等价的：

select distinct p.firstname, p.middlename, p.lastname,
  p.gender, p.dob, p.id as patientid,
  pr.id as practiceid, pr.[name] as practicename, pr.parentaco,
  pp.encounterdate
  from aco.patients_practices as patients_practices inner join
    aco.patients as p on p.id = patients_practices.patientid inner join
    aco.practices as pr on pr.id = patients_practices.practiceid inner join
    aco.patientpreferences as pp on pp.patientid = p.id
  where pp.encounterdate is not null and
    ( pr.parentaco = ( select parentaco from aco.practices where master_companyid = 763 ) or
    pr.id = ( select parentaco from aco.practices where master_companyid = 763 ) )

关于WHERE子句中的重复子查询：查询优化器应该识别重复并相应地处理它。您可以通过检查执行计划来确认这一点。使用Mikael Eriksson的建议来捕获变量中的parentaco应该可以解决这个问题。

Answer 3

为什么在where条件中两次使用相同的条件？你第二次是想用不同的身份证吗？

为什么不直接加入实践表，然后在那里做一个where条件，而不进行子选择？

Answer 4

这可能不是100%，但希望能引导您进入正确的状态。

SELECT DISTINCT p.firstname          ,
            p.middlename             ,
            p.lastname               ,
            p.gender                 ,
            p.dob                    ,
            p.id      AS patientid   ,
            pr.id     AS practiceid  ,
            pr.[name] AS practicename,
            pr.parentaco             ,
            pp.encounterdate
FROM        aco.patients_practices pp
            LEFT OUTER JOIN aco.patients p
              ON  pp.patientid = p.id  
            LEFT OUTER JOIN aco.practices pr
              ON  pp.practiceid = pr.id
              AND ( pr.parentaco = pp.parentco or pr.id = pp.parentco )
WHERE    pp.encounterdate IS NOT NULL
         AND pp.master_companyid = 763