可以的。首先,让我们看看如何获取您设置中具有最小和最大日期的记录:
最小值:
select top 1 Name, DateOfBirth
from yourtable
order by DateOfBirth
Max:
select top 1 Name, DateOfBirth
from yourtable
order by DateOfBirth desc
这是获取匹配记录的方法:
select top 1 Name, DateOfBirth
from yourtable
where DateOfBirth = @InputDate
现在,让我们把所有东西整合到一个查询中:
select mymin.Name as myminName, mymin.DateOfBirth as myminDateOfBirth,
mymax.Name as mymaxName, myMax.DateOfBirth as mymaxDateOfBirth,
therecord.Name as therecordName, therecord.DateOfBirth as therecordDateOfBirth
from
(select top 1 Name, DateOfBirth
from yourtable
order by DateOfBirth) mymin
join
(select top 1 Name, DateOfBirth
from yourtable
order by DateOfBirth desc) mymax
on 1 = 1
left join yourtable therecord
on therecord.DateOfBirth = @InputDate
正如您所看到的,我们可以 select
所有可能的值。最后一步是修改选择以仅获取所需记录的 Name
和 DateOfBirth
。如果没有匹配项并且日期不小于最小日期而且不大于最大日期,则返回 null
。为此,我们需要使用 case
-when
语法,像这样:
select case (therecord.Name is null)
When 1 then (case mymin.DateOfBirth > @InputDate when 1 then mymin.Name
else case mymax.DateOfBirth < @InputDate when 1 then mymax.Name else null end)
Else therecord.Name
End as Name,
case (therecord.Name is null)
When 1 then (case mymin.DateOfBirth > @InputDate when 1 then mymin.DateOfBirth
else case mymax.DateOfBirth < @InputDate when 1 then mymax.DateOfBirth else null end)
Else therecord.DateOfBirth
End as DateOfBirth
from
(select top 1 Name, DateOfBirth
from yourtable
order by DateOfBirth) mymin
join
(select top 1 Name, DateOfBirth
from yourtable
order by DateOfBirth desc) mymax
on 1 = 1
left join yourtable therecord
on therecord.DateOfBirth = @InputDate
我假设您使用的是SQL Server。
警告:这段代码没有经过测试,如果有任何拼写错误,请告诉我。