Access alias in subquery

问题:

I need to access an alias from the outer query in the subquery, like this code. Except it is not working. Is there a way to do this? My goal is to make these aggregate functions for every OwnerIdName and this is the only way I can think of. I am working in SQL Server 2008.

Thanks

SELECT Incident.OwnerIdName as OIN
  ,(SELECT COUNT(*)
    FROM Incident 
    WHERE CreatedOn BETWEEN '2011/1/1' AND '2011/1/31'
    AND Incident.OwnerIdName = OIN ) as CasesOpened
  ,(SELECT COUNT(*)
    FROM IncidentResolution 
    WHERE ActualEnd BETWEEN '2011/1/1' AND '2011/1/31'
            AND Incident.OwnerIdName = OIN ) as CasesClosed
FROM Incident

回答1:

Can't you use the column name directly..? Like..

SELECT Inc.OwnerIdName as OIN
  ,(SELECT COUNT(*)
    FROM Incident 
    WHERE CreatedOn BETWEEN '2011/1/1' AND '2011/1/31'
    AND Incident.OwnerIdName = Inc.OwnerIdName ) as CasesOpened
  ,(SELECT COUNT(*)
    FROM IncidentResolution 
    WHERE ActualEnd BETWEEN '2011/1/1' AND '2011/1/31'
            AND Incident.OwnerIdName = Inc.OwnerIdName ) as CasesClosed
FROM Incident Inc

If you want to use the Alias directly, you will have to do that early on in your query and then use it for all the Scalar Sub-Queries..

SELECT INC.OIN
  ,(SELECT COUNT(*)
    FROM Incident 
    WHERE CreatedOn BETWEEN '2011/1/1' AND '2011/1/31'
    AND Incident.OwnerIdName = INC.OIN ) as CasesOpened
  ,(SELECT COUNT(*)
    FROM IncidentResolution 
    WHERE ActualEnd BETWEEN '2011/1/1' AND '2011/1/31'
            AND Incident.OwnerIdName = INC.OIN ) as CasesClosed
from 
(Select OwnerIdName OIN
  FROM Incident) INc 

回答2:

Try this(works for >= SQL 2005):

WITH 
    CreatedQry AS
    (
        SELECT OwnerIdName, COUNT(1) AS CreatedCount
          FROM  Incident 
        WHERE CreatedOn BETWEEN '2011/1/1' AND '2011/1/31'
    ),
    EndQry AS
    (
        SELECT OwnerIdName, COUNT(1) AS EndCount
          FROM  IncidentResolution  
        WHERE ActualEnd  BETWEEN '2011/1/1' AND '2011/1/31'
    )
    SELECT  CreatedQry.OwnerIdName, CreatedCount, EndCount
      FROM  CreatedQry LEFT JOIN EndQry
        ON  CreatedQry.OwnerIdName = EndQry.OwnerIdName