select nID,min(abs(nCount-@tMin)) as nMin,
max(abs(nCount-@tMax)) as nMax
from T1 where nCount>=@tMin-10 or nCount<=@tMax+10
group by nID
max(abs(nCount-@tMax)) as nMax
from T1 where nCount>=@tMin-10 or nCount<=@tMax+10
group by nID
( case when exists(select 1 from T1 where nCount between @tMin-10 and @tMax+10) then max(abs(nCount-@tMax)) else -1 end) as nMax
from T1 group by nID
select nID,isnull(min(case when nCount between @tMin-10 and @tMax+10 then abs(nCount-@tMin) end),-1) nMin,isnull(max(case when nCount between @tMin-10 and @tMax+10 then abs(nCount-@tMax) end),0) nMax
from T1
group by nID
declare @T1 table(tID varchar(4),tEvent varchar(10))
insert into @T1
select '001','上课'
union all select '002','上课'
union all select '003','上课'
union all select '004','上课'
union all select '002','下课'
union all select '008','下课'declare @T2 table(tID varchar(4),tCount int)
insert into @T2
select '001',100
union all select '001',90
union all select '002',80
union all select '002',90
union all select '002',10
union all select '002',80
union all select '003',80
union all select '004',70--查询条件
declare @tEvent varchar(10)
set @tEvent='上课'
declare @InputMin int,@InputMax int
select @InputMin=80,@InputMax=90--查询结果
select a.tID,tMin=isnull(b.tMin,-1),tMax=isnull(b.tMax,-1)
from @T1 a left join(
select a.tID,tMin=a.tCount,tmax=b.tCount
from(
select tID,tCount=max(tCount)
from @T2 a
where tCount between @InputMin-10 and @InputMax+10
and abs(tCount-@InputMin) in(select min(abs(tCount-@InputMin)) from @T2 where a.tID=tID)
group by tID
) a inner join (
select tID,tCount=max(tCount)
from @T2 a
where tCount between @InputMin-10 and @InputMax+10
and abs(tCount-@InputMax) in(select min(abs(tCount-@InputMax)) from @T2 where a.tID=tID)
group by tID
) b on a.tID=b.tID
) b on a.tID=b.tID
where a.tEvent=@tEvent
http://expert.csdn.net/Expert/topic/2287/2287145.xml?temp=.3382227
nMin=abs(nCount-tMin)最小的一个,同时nCount IN [tMin-10,tMax+10]
nMax=abs(nCount-tMAX)最小的一个,同时nCount IN [tMin-10,tMax+10]
如果某个ID无记录或者在 [tMin-10,tMax+10]中无记录nMin=nMax=-1 ????????
nMin=abs(nCount-tMin)最小的一个,同时nCount IN [tMin-10,tMax+10]
nMax=abs(nCount-tMAX)最小的一个,同时nCount IN [tMin-10,tMax+10]
如果某个ID无记录或者在 [tMin-10,tMax+10]中无记录nMin=nMax=-1 --查询条件
declare @tEvent varchar(10)
set @tEvent='上课'
declare @InputMin int,@InputMax int
select @InputMin=80,@InputMax=90--查询结果
select a.tID,tMin=isnull(b.tMin,-1),tMax=isnull(b.tMax,-1)
from @T1 a left join(
select a.tID,tMin=a.tCount,tmax=b.tCount
from(
select tID,tCount=max(tCount)
from @T2 a
where tCount between @InputMin-10 and @InputMax+10
and abs(tCount-@InputMin) in(select min(abs(tCount-@InputMin)) from @T2 where a.tID=tID)
group by tID
) a inner join (
select tID,tCount=max(tCount)
from @T2 a
where tCount between @InputMin-10 and @InputMax+10
and abs(tCount-@InputMax) in(select min(abs(tCount-@InputMax)) from @T2 where a.tID=tID)
group by tID
) b on a.tID=b.tID
) b on a.tID=b.tID
where a.tEvent=@tEvent