SQL Distinct Count and Case when

Question

这是我对Microsoft SQL Server的查询:

select count(distinct AEN) as Customers  
from 
(   select top 100 ERDAT, AEN,  
case 
when AEN = 'MBLE' AND ERDAT between '20220401' and '20220430' then 'Mobile'
when AEN = 'ISU' AND ERDAT between '20220401' and '20220430' then 'Website'
when AEN = 'CRM' AND ERDAT between '20220401' and '20220430' then 'CRM'
when AEN like '_[0-9]%' AND ERDAT between '20220401' and '20220430' then 'ID Number'
else 'Others'
end as 'Channels' 
from [Table]
)

显然括号有问题。任何帮助都非常感激!:)

Answer 1

正如在评论中已经提到的，您需要子查询的别名。

改变这

count(distinct AEN) as Customers  

到

count(distinct t.AEN) as Customers  

还有这个

[table]
)

到

[table]
) t

看这个DBFiddle

这将运行没有错误，但我不知道如果这是你想要实现的，你的问题是不清楚的