在WHERE语句使用子查询的别名(Using subquery's alias in a W

我想用在SELECT创建的别名，但在WHERE语句。 我知道它不工作，我只是了解为什么在另一个SO问题。

但我的问题是：我应该采取什么其他的解决办法，使这项工作不重复的子查询？

SELECT p.PatientID, p.PatientType, p.AccountNumber, p.FirstName + ' ' + p.LastName PatientFullName, p.CreatedDate,
DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime,
(SELECT AVG(BGValue) FROM BloodGlucose WHERE PatientID = p.PatientID) AvgBG
FROM Patients p
WHERE AvgBG > 60;

我知道这工作：

SELECT p.PatientID, p.PatientType, p.AccountNumber, p.FirstName + ' ' + p.LastName PatientFullName, p.CreatedDate,
DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime,
(SELECT AVG(BGValue) FROM BloodGlucose WHERE PatientID = p.PatientID) AvgBG
FROM Patients p
WHERE (SELECT AVG(BGValue) FROM BloodGlucose WHERE PatientID = p.PatientID) > 60;

但我不想重复子查询。 而且我敢肯定它是不是非常的性能代价所以这是我要求一个更好的解决方案在这里的原因。

谢谢！

Answer 1:

尝试使用派生表来代替。

SELECT p.PatientID, p.PatientType, p.AccountNumber, p.FirstName + ' ' + p.LastName PatientFullName, p.CreatedDate, DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime, bg.AvgBG 
FROM Patients p 
JOIN (SELECT PatientID, AVG(BGValue) AvgBG   FROM BloodGlucose group by PatientID ) BG
    ON BG.PatientID = p.PatientID 
WHERE AvgBG > 60; 

Answer 2:

WHERE子句中的别名只能来自FROM子句。 这里有一个方法来重写查询：

SELECT p.PatientID, p.PatientType, p.AccountNumber,
       p.FirstName + ' ' + p.LastName as PatientFullName, p.CreatedDate,
       DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime,
       av.AvgBG
FROM Patients p join
     (SELECT PatientId, AVG(BGValue) as AvgBG
      FROM BloodGlucose
      group by PatientID
     ) av
     on p.PatientId = av.PatientId
WHERE av.AvgBG > 60;

Answer 3:

SELECT p.PatientID, p.PatientType, p.AccountNumber, p.FirstName + ' ' + p.LastName PatientFullName, p.CreatedDate, DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime, bg.AvgBG  
FROM Patients p  
outer apply (SELECT PatientID, AVG(BGValue) AvgBG   
FROM BloodGlucose where PatientID = p.PatientID  
 group by PatientID ) BG 
 WHERE AvgBG > 60; 

这也应该工作很快

Answer 4:

你可以尝试用标准的SQL子句中使用。

WITH Subquery AS
(
    Your Code
)

然后你的代码

SELECT ....
Subquery As sq
From Patients p
WHERE(subquery)>60

Answer 5:

使用公用表表达式：

;WITH CTE (PatientID, PatientType, AccountNumber, PatientFullName, CreatedDate, TotalTime, AvgBG) AS (
    SELECT p.PatientID, p.PatientType, p.AccountNumber, p.FirstName + ' ' + p.LastName PatientFullName, p.CreatedDate,
    DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime,
    (SELECT AVG(BGValue) FROM BloodGlucose WHERE PatientID = p.PatientID) AvgBG
    FROM Patients p
)

SELECT PatientID, PatientType, AccountNumber, PatientFullName, CreatedDate, TotalTime, AvgBG
FROM CTE
WHERE AvgBG > 60

Answer 6:

SELECT p.PatientID, p.PatientType, p.AccountNumber, p.FirstName p.LastName PatientFullName, p.CreatedDate,
DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime, a.AvgBG
FROM Patients p,
(SELECT PatientID, AVG(BGValue) AvgBG
  FROM BloodGlucose
group by PatientID
having AVG(BGValue) > 60) a
where p.PatientID = a.PatientID

文章来源: Using subquery's alias in a WHERE statement