34,591
社区成员
发帖
与我相关
我的任务
分享
select * from Class_info where CONVERT(nvarchar,class_outyear) + '/07' >= '1998/09') AND CONVERT(nvarchar,class_inyear) + '/09' <= '1998/09'
create table tb(class_id int, class_name varchar(10), class_inyear int, class_outyar int)
insert into tb values(1, '十班' , 1995, 1998 )
insert into tb values(2, '十二班', 1995, 1998 )
insert into tb values(3, '十五班', 1996, 1999 )
insert into tb values(4, '十八班', 1997, 2000 )
insert into tb values(5, '二十班', 1998, 2001 )
insert into tb values(6, '三十班', 1999, 2002 )
go
declare @dt as varchar(7)
set @dt = '1998/09'
select * , class_term =
case
when class_outyar > cast(left(@dt , charindex('/',@dt) - 1) as int) and substring(@dt , charindex('/',@dt) + 1 , 2) = '09' then (cast(left(@dt , charindex('/',@dt) - 1) as int) - class_inyear) * 2 + 1
when class_outyar > cast(left(@dt , charindex('/',@dt) - 1) as int) and substring(@dt , charindex('/',@dt) + 1 , 2) = '03' then (cast(left(@dt , charindex('/',@dt) - 1) as int) - class_inyear) * 2
end
from tb where class_outyar > cast(left(@dt , charindex('/',@dt) - 1) as int) and cast(left(@dt , charindex('/',@dt) - 1) as int) >= class_inyear
/*
class_id class_name class_inyear class_outyar class_term
----------- ---------- ------------ ------------ -----------
3 十五班 1996 1999 5
4 十八班 1997 2000 3
5 二十班 1998 2001 1
(3 行受影响)
*/
drop table tb
select (if *** then *** ) as class_term from Class_info where CONVERT(nvarchar,class_outyear) + '/07' >= '1998/09') AND CONVERT(nvarchar,class_inyear) + '/09' <= '1998/09'
select *,
case 参数月份
when '03' then (参数年份-入学年份)*2+1
else (参数年份-入学年份)*2
end as class_term
from Class_info where
CONVERT(nvarchar,class_outyear) + '/07' >= '1998/09' AND
CONVERT(nvarchar,class_inyear) + '/09' <= '1998/09'