I'm trying to use an alias created in a SELECT in a WHERE statement. It doesn't work and I read why in another SO question.
How do I make this work without repeating the subquery?
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;
This works:
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;
But I don't want to repeat that subquery. And I suspect it has poor performance.
6条答案
按热度按时间gblwokeq1#
Try using a derived table instead.
Derived tables work in sets and correlated subqueries work row-by-agonizing-row, that is why mine is faster.
mwyxok5s2#
The aliases in the WHERE clause can only come from the FROM clause. Here is a way to rewrite your query:
oknwwptz3#
this should also work pretty quickly
vybvopom4#
egdjgwm85#
You can try to use WITH clause in standard SQL.
And then your code
ttvkxqim6#
Use a common table expression: