問題描述

我需要從子查詢中的外部查詢訪問別名，就像這段代碼.除了它不工作.有沒有辦法做到這一點?我的目標是為每個 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 

這篇關于子查詢中的訪問別名的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！