字母：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
/*
ap number
X 19
*/
------解决方案--------------------
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)
*/
------解决方案--------------------
求除法后的余数。