sqlserver 资料库
有这样一个表 test
有栏位 其中date1 与 date2 为日期型
id date1 date2
1 2011-11-08 NULL
2 NULL 2011-10-03
3 NULL NULL
4 2011-10-09 2011-11-08
5 2012-01-09 2017-11-07
现在要求得到数据
id date1 date2 a1 b1
1 2012-11-08 NULL N N
2 NULL 2011-10-03 N Y
3 NULL NULL N N
4 2011-10-09 2011-11-08 Y Y
5 2012-01-09 2017-11-07 Y N
其中 当 date1 中的日期 小于当前日期+180 天时，a1中显示 Y 否则显示N
其中 当 date2 中的日期 小于当前日期+30 天时，a2中显示 Y 否则显示N
------解决方案--------------------
- SQL code
select *, a1=case when date1<dateadd(day,180,getdate()) then 'Y' else 'N' end, a2=case when date2<dateadd(day,180,getdate()) then 'Y' else 'N' endfrom tb
------解决方案--------------------
- SQL code
create table tb(id int,date1 datetime,date2 datetime)insert into tb select 1,'2011-11-08',null union select 2,null,'2011-10-03' select *, a1=case when date1<dateadd(day,180,getdate()) then 'Y' else 'N' end, a2=case when date2<dateadd(day,30,getdate()) then 'Y' else 'N' endfrom tb/*id date1 date2 a1 a2----------- ----------------------- ----------------------- ---- ----1 2011-11-08 00:00:00.000 NULL Y N2 NULL 2011-10-03 00:00:00.000 N Y(2 行受影响)
------解决方案--------------------
- SQL code
select *, a1=case when datediff(day,date1,getdate())>180 then 'Y' else 'N' end, a2=case when datediff(day,date1,getdate())>180 then 'Y' else 'N' endfrom tb
------解决方案--------------------
楼主的结果不对吧
- SQL code
if object_id('test') is not null drop table testgocreate table test( id int identity(1,1), date1 datetime, date2 datetime)goinsert into test(date1,date2)select '2011-11-08',null union allselect null,'2011-10-03' union allselect null,null union allselect '2011-10-09','2011-11-08' union allselect '2012-01-09','2017-11-07'goselect * , a1=case when date1<dateadd(dd,180,getdate()) then 'Y' else 'N' end, a2=case when date2<dateadd(dd,30,getdate()) then 'Y' else 'N' endfrom testgo/*id date1 date2 a1 a2----------- ----------------------- ----------------------- ---- ----1 2011-11-08 00:00:00.000 NULL Y N2 NULL 2011-10-03 00:00:00.000 N Y3 NULL NULL N N4 2011-10-09 00:00:00.000 2011-11-08 00:00:00.000 Y Y5 2012-01-09 00:00:00.000 2017-11-07 00:00:00.000 Y N(5 行受影响)*/