表mess结构说明如下:
id effectime pubdate
1 1 2004-4-19 21:30:59
2 3 2004-4-16 20:35:24
3 7 2004-4-18 14:25:36
4 3 2004-4-22 16:15:34
5 7 2004-4-23 15:45:24
假如要查询三日内发布信息(隐藏条件是该信息没有过有效期),那么查询语句该写成:
select *
from mess
where datediff(day,pubdate,getdate())<varday 'varday表示从查询页面传递过来的参数即多少天,为数值型,现在就是如何同时判断它是否过期,该如何写呢?只用一条语句的话
id effectime pubdate
1 1 2004-4-19 21:30:59
2 3 2004-4-16 20:35:24
3 7 2004-4-18 14:25:36
4 3 2004-4-22 16:15:34
5 7 2004-4-23 15:45:24
假如要查询三日内发布信息(隐藏条件是该信息没有过有效期),那么查询语句该写成:
select *
from mess
where datediff(day,pubdate,getdate())<varday 'varday表示从查询页面传递过来的参数即多少天,为数值型,现在就是如何同时判断它是否过期,该如何写呢?只用一条语句的话
from mess
where effectime=varday
SELECT ysid,a.yscity1,a.yscity2,a.yspubdate,a.ystool,b.company,c.province as province1,d.province as province2,e.effectime,是否过期=case when datediff(day,yspubdate,getdate())<varday
else
end
FROM dbo.t_yunshu a left join dbo.t_company b on a.cid=b.id left join dbo.t_province c on a.yspro1=c.pid left join dbo.t_province d on a.yspro2=d.pid left join dbo.t_effectime e on a.yseffecttime=e.etid
WHERE a.cid like 'varcid' and a.yspro1=varpro1 and a.yscity1='varcity1' and a.yspro2=varpro2 and a.yscity2='varcity2' and a.ysprotype like '%vartype%' and a.ystool like '%vartool%' and a.ysasktime like '%vartime%' and datediff(day,yspubdate,getdate())<vareffect+1
ORDER BY varziduan varshunxu出现提示:在关键字else附近有语法错误,问题在哪里呢?varday我已经给它赋值了,request("yseffecttime")就是从查询页面传递过来的
else
end改为:
是否过期=case when datediff(day,yspubdate,getdate())<varday then 0 --未过期
else 1 --已经过期
end