• 主页
• 基础类
• 应用实例
• 新技术前沿

# 请教:如何根据程序提交的参数来和数据表中的记录计算并显示其值?

chuxinfo 2008-01-03 09:01:29

class_id class_name class_inyear class_outyar
1 十班 1995 1998
2 十二班 1995 1998
3 十五班 1996 1999
4 十八班 1997 2000
5 二十班 1998 2001
6 三十班 1999 2002
'------------------------------------------------
class_inyear和class_outyar为(int型)代表入学和毕业年份,入学1995也就是1995/09;毕业1998也就是1998/07

(一年级为:1-2学期)(二年级为:3-4学期)(三年级为:5-6学期),如果毕业的班级就不显示计算其值了

class_id class_name class_inyear class_outyear class_term
3 十五班 1996 1999 5
4 十八班 1997 2000 3
5 二十班 1998 2001 1
'-----------------------------------------------

``select * from Class_info where CONVERT(nvarchar,class_outyear) + '/07' >= '1998/09') AND CONVERT(nvarchar,class_inyear) + '/09' <= '1998/09'``

...全文
34 点赞 收藏 5

5 条回复

chuxinfo 2008-01-03

dawugui 2008-01-03
``````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``````

chuxinfo 2008-01-03

select *,
case Right('1998/09',2)
when '03' then (Left('1998/09',4)-class_inyear)*2+1
else (Left('1998/09',4)-class_inyear)*2
end as class_term
from Class_info where
CONVERT(nvarchar,class_outyear) + '/07' >= '1998/09' AND
CONVERT(nvarchar,class_inyear) + '/09' <= '1998/09'

chuxinfo 2008-01-03

``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'
``````

MS-SQL Server

3.2w+

MS-SQL Server相关内容讨论专区