根据年份返回字母
字母：ABCDEFGHJKLMNPRSTVWX
2010=A
2011=B
....
2029=X
2030=A
20年轮一回，这个能实现吗？
[解决办法]
真没必要循环吧...
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-17 17:23:30
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：test
if object_id('test') is not null drop table test
go
create table test([id] INT IDENTITY(1,1),[letter] varchar(20))
insert test(letter)
select 'A'
UNION ALL
SELECT 'B'
UNION ALL
SELECT 'C'
UNION ALL
SELECT 'D'
--------------开始查询--------------------------
select *,CONVERT(CHAR(4),DATEADD(YEAR,id,'2009-01-01'),120)[Year]
from test
----------------结果----------------------------
/*
id letter Year
----------- -------------------- ----
1 A 2010
2 B 2011
3 C 2012
4 D 2013
*/
;with t
as
(
select 2010 year,'A' ap,0 number union all
select 2011 year,'B' ap,1 number union all
select 2012 year,'C' ap,2 number union all
select 2013 year,'D' ap,3 number union all
select 2014 year,'E' ap,4 number union all
select 2015 year,'F' ap,5 number union all
select 2016 year,'G' ap,6 number union all
select 2017 year,'H' ap,7 number union all
select 2018 year,'J' ap,8 number union all
select 2019 year,'K' ap,9 number union all
select 2020 year,'L' ap,10 union all
select 2021 year,'M' ap,11 union all
select 2022 year,'N' ap,12 union all
select 2023 year,'P' ap,13 union all
select 2024 year,'R' ap,14 union all
select 2025 year,'S' ap,15 union all
select 2026 year,'T' ap,16 union all
select 2027 year,'V' ap,17 union all
select 2028 year,'W' ap,18 union all
select 2029 year,'X' ap,19
)
--2045年就是S
select ap,number
from t
where (2049- 2010) % 20 = number
/*
apnumber
X19
*/
create function dbo.fn_getyearcode
(@y int) returns char(1)
as
begin
declare @c char(1)
select @c=substring('ABCDEFGHJKLMNPRSTVWX',
case (@y-2009)%20 when 0 then 20 else (@y-2009)%20 end,
1)
return @c
end
-- test1
select dbo.fn_getyearcode(2010) 'code'
/*
code
----
A
(1 row(s) affected)
*/
-- test2
select dbo.fn_getyearcode(2011) 'code'
/*
code
----
B
(1 row(s) affected)
*/
-- test3
select dbo.fn_getyearcode(2029) 'code'
/*
code
----
X
(1 row(s) affected)
*/
-- test4
select dbo.fn_getyearcode(2030) 'code'
/*
code
----
A
(1 row(s) affected)
*/
