在 SQL 中的什么位置插入 CASE 语句?

Question

这里的问题是，虽然这在 SSMS 中有效，但当我将其发布到使用 SQL 处理工具的网站上时，它会失败。我已将其缩小到 C3 部分中的第一个粗体部分。在此之前使用代码，它会给出一个错误(就像它应该的那样(。添加该行，网站崩溃。我使用其他查询作为实验，没问题。虽然我不能保证它会起作用，但我猜它不喜欢我的周期年字段是 char(4(，并且它不能进行从 char 到 int 然后回到 char 的隐式转换。所以我的问题是我将把第二行粗体放在 C3 部分(或之前(的什么位置？

WITH c1 AS
(SELECT e.stfips, e.areatype, e.area, e. periodyear, e.period, e.seriescode, e.empces
FROM ces as e
WHERE e.periodtype='03'
And e.supprecord='0'
and e.periodyear=
(Select Max(periodyear)
From ces)
and e.period=
(Select Top(1)period
From ces
Order by periodyear desc, period desc)
and e.stfips='32'
and e.adjusted='1'
and e.areatype='01'
),
C2 AS
(Select Distinct c1.periodyear, c1.period
From c1
),
C3 As
(Select
**(Case When c2.period='01' Then (c2.period + 11) Else (c2.period-1) END) As 'month',**
(Case When c2.period='01' Then (c2.periodyear -1) Else (c2.periodyear) END) As 'year'
From C2
),
C4 AS
(Select c.stfips, c.areatype, c.area, c.periodyear, c.period, c.seriescode, c.empces
From ces as c, c2
Where c.period = c2.period 
**And c.periodyear = ( cast ( cast(c2.periodyear as int) -1 ) as nvarchar(10) )**
And c.supprecord='0'
and c.stfips='32'
and c.adjusted='1'
and c.areatype='01'
),

Answer 1

您可以使用year函数，而不是cast或隐式转换

year(c.periodyear) = year(c2.periodyear)-1 

编辑：(添加C3部分(

Select
Case When c2.period='01' Then (c2.period + 11) Else (c2.period-1) END) As 'month',
Case When c2.period='01' Then year(c2.periodyear) -1 Else year(c2.periodyear) END As 'year'
From C2