时间戳

首页 > 解决方案 > 使用选定值计算新值

sql - 使用选定值计算新值

问题描述

select campaign
,ISNULL(sum(cast (HandleTime as bigint)), 0) handleTime
,(select Contacts from [View_ContactsByCampaign] Con where Con.Campaign = C.Campaign) Contacts
, ContactPerTime = Contacts / handleTime
from call C
group by campaign

我在计算 ContactPerTime 值时遇到问题。我收到“无效的列名‘联系人’”。和“无效的列名'handleTime'”错误。是否可以使用这些值进行计算？

标签： sqlsql-servertsql

解决方案

您不能通过SELECT别名引用语句中的另一列。如果您再次需要该表达式，则需要重复该表达式：

SELECT campaign,
       ISNULL(SUM(CAST (HandleTime AS bigint)), 0) AS handleTime,
       (SELECT Contacts
        FROM [View_ContactsByCampaign] Con
        WHERE Con.Campaign = C.Campaign) AS Contacts,
       (SELECT Contacts
        FROM [View_ContactsByCampaign] Con
        WHERE Con.Campaign = C.Campaign) / handleTime AS ContactPerTime
FROM [CALL] C
GROUP BY campaign;

但是，由于您拥有的是子查询，因此最好使用APPLY. 大概是这样的::

SELECT campaign,
       ISNULL(SUM(CAST (HandleTime AS bigint)), 0) AS handleTime,
       Con.Contacts
       Con.Contacts / handleTime AS ContactPerTime
FROM [CALL] C
     CROSS APPLY(SELECT Contacts
                 FROM [View_ContactsByCampaign] ca
                 WHERE ca.Campaign = C.Campaign) Con
--Considering that Contacts was returned from a subquery, it's likely a "safe"
--assumption it will return 1 row. If it didn't, then your previous query
--would have errored anyway.
GROUP BY campaign, Contacts;

作为参考，请查看语句的逻辑处理顺序SELECT：其中指出：

相反，因为SELECT子句是第 8 步，所以在该子句中定义的任何列别名或派生列都不能被前面的子句引用。但是，它们可以被后续的子句（例如ORDER BY子句）引用。

ORDER BY实际上是唯一可以在语句的同一部分通过别名引用列的地方。

推荐阅读