将varchar转换为数据类型数字CASE语句时发生算术溢出错误

Question

我正试图编写一个返回"估计年值"的查询，为此，我使用Case语句。查询中涉及两个内连接。

所以，查询和给我的结果，当我运行这段:

select Users.Id, Name, PhoneNumber, Email, Currency, count(*) as TotalOrders, sum(TotalCustomerAmount) as TotalOrderValue, avg(TotalCustomerAmount) as AverageOrderValue, TsCreate as RegistrationDate, max(TsOrderPlaced) as LastOrderDate, min(TsOrderPlaced) as FirstOrderDate, 
CASE 
When PromotionsEnabled = 0 then 'Y'
When PromotionsEnabled = 1 then 'n'
else 'undefined'
end as Promotions,
/*CASE
When ((DATEDIFF(day, max(TsOrderPlaced), min(TsOrderPlaced)) >= 6) AND (count(*) > 3)) then  ((sum(TotalCustomerAmount)/(DATEDIFF(day, max(TsOrderPlaced), min(TsOrderPlaced))))*365) 
Else '-'
end as EstimatedAnnualValue*/
from AspNetUsers with (nolock)
inner join Orders with (nolock) on Orders.UserId = AspNetUsers.Id and Orders.WhiteLabelConfigId = @WhiteLabelConfigId
and Orders.OrderState in (2,3,4,12)     
inner join UserWhiteLabelConfigs with (nolock) on UserWhiteLabelConfigs.UserId = AspNetUsers.Id and Orders.WhiteLabelConfigId = @WhiteLabelConfigId
where AspNetUsers.Discriminator = 'ApplicationUser'
group by Users.Id, Name, Number, Currency, Email, TsCreate, PromotionsEnabled

但是问题来了，当我用第二个CSAE语句运行这个，是因为我不能在GROUP BY之前使用聚合函数吗?我也在考虑使用子查询寻找一些帮助!!

Answer 1

您需要使用聚合函数。例如，如果只在所有值都是0或NULL时才需要'Y':

(case when max(PromotionsEnabled) = 0 then 'Y'
when max(PromotionsEnabled) = 1 then 'N'
else 'undefined'
end) as Promotions,

我不确定这是否是您想要的逻辑(因为该细节不在问题中)。但是，这表明您可以在case表达式中使用聚合函数.