子查询中的访问别名

问题描述

我需要从子查询中的外部查询访问别名，就像这段代码.除了它不工作.有没有办法做到这一点?我的目标是为每个 OwnerIdName 制作这些聚合函数，这是我能想到的唯一方法.我在 SQL Server 2008 中工作.

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.

谢谢

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

推荐答案

不能直接用列名..?喜欢..

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