sql - SQL SERVER 中的分组依据/打印相关查询

Question

好吧，我有一个表，其中包含 custid、lastname、firstname 和 DOB。

我的任务是再创建一个列，其中包含表示客户出生年份的字符串...

即如果客户出生于 1950 年，则该唱片的新专栏应该说，五十年代，如果客户出生于 1974 年，则该唱片的新栏目应该是“七十年代”。等等。

我认为我必须按每个十年分组并创建一个新专栏，以“字符串”表示客户诞生的十年。

这是我的尝试...

Select DateOfBirth, COUNT(DateOfBirth) as 'DOBYEAR' From Customers
Group by DateOfBirth
Order By DateOfBirth asc
If (Customers.DateOfBirth <= '1979')
begin
set DOBYEAR = 'Seventies and Before'
end
If (Customers.DateOfBirth between '1980' and '1989)'
begin
set DOBYEAR = 'Eighties'
end
If (Customers.DateOfBirth between '1990' and '1999')
begin
set DOBYEAR = 'Nineties'
end
If Customers.DateOfBirth >= '2000'
Begin
set DOBYEAR = '20th century'
end
Go

我正在创建 DOBYEAR 作为新列并尝试输入字符串，例如“seventies and before, eighties, nitents, and 20th century....

给我以下错误。

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near '='.

请指引我正确的方向。

我可以使用“Print 'Seventies and Before'”而不是“set DOBYEAR = 'Seventies and Before'”吗？

也许，我可以在不使用 IF 语句的情况下做到这一点，但不知道如何做到这一点。

谢谢

Answer 1

您不需要 group by 来执行此操作。假设 DateOfBirth 列的类型为 datetime(提示:它应该):

Select 
    DateOfBirth, 
    Case 
      when datepart(year, DateOfBirth) between 1970 and 1979 then 'Seventies' 
      when datepart(year, DateOfBirth) between 1980 and 1989 then 'Eighties' 
      when datepart(year, DateOfBirth) between 1990 and 1999 then 'Nineties' 
      when datepart(year, DateOfBirth) >= 2000 then '21st century' 
      else 'Before 1970' 
    end as DOBDecadeString
From 
   Customers
Order By DateOfBirth asc