注:如果分别需要生成两张表:一张是所有符合条件的记录,即
在T2中查找所有“上课”事件的ID,如果某个ID在T2中有多个记录,
取其abs(tCount-90)最小,同时tCount >=80 AND tCount <=90另外一张表:
即在T2中没有记录或者在T2中有记录但是在tCount>=80 AND tCount<100时候没有记录!
这两次查询又分别怎么做呢???
在T2中查找所有“上课”事件的ID,如果某个ID在T2中有多个记录,
取其abs(tCount-90)最小,同时tCount >=80 AND tCount <=90另外一张表:
即在T2中没有记录或者在T2中有记录但是在tCount>=80 AND tCount<100时候没有记录!
这两次查询又分别怎么做呢???
解决方案 »
- 求一BBS论坛数据库设计文档
- 请问递增主键用聚集索引还是非聚集索引 哪个好?
- 存储过程,变量做表名,返回值的问题.
- 深夜求算法..打扰了~~~~~~~~~~~~~~~~~~~sql语句~~~~~~~~~~~~~~~~~高手没睡的来.~~~~~~~~~~~~~~~~~
- 函数设计
- 我想让Sql server2000,在满足一定的条件的时候,备分已有的数据库。
- 请教,由于文件 ID 15232无效,所以未能打开 FCB
- 大家帮忙解决一下,谢谢!
- 数据库路径问题,急!
- 主题:有谁能给我分析一下sql server日志文件吗?谢了先!
- 数据库管理
- 请问:SQL的DATETIME类型字段,只要取它的日期,不要时间(时分秒)要怎样写?
and tCount >=80 AND tCount <=90 having abs(tcount-90)=min(abs(tcount-90))
declare @tEvent varchar(10)
set @tEvent='上课'--第一个表(取其abs(tCount-90)最小,同时tCount >=80 AND tCount <=90)
select distinct *,newtCount=abs(tCount-90) from T2 a
where tID in(select tID from T1 where tEvent=@tEvent)
and tCount between 80 and 90
and abs(tCount-90)=(select min(abs(tCount-90)) from T2 where a.tID=tID)--第二个表(在T2中没有记录或者在T2中有记录但是在tCount>=80 AND tCount<100时候没有记录)
select distinct a.tID,-99
from T1 a left join T2 b on a.tID=b.tID
where a.tEvent=@tEvent and b.tID is null or (
(b.tCount<80 or b.tCount>90)
and a.tID not in(select tID from t2 where tCount between 80 and 90))
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='下课'--第一个查询
select distinct *,newtCount=abs(tCount-90) from @T2 a
where tID in(select tID from @T1 where tEvent=@tEvent)
and tCount between 80 and 90
and abs(tCount-90)=(select min(abs(tCount-90)) from @T2 where a.tID=tID)--第二个查询
select distinct a.tID,-99
from @T1 a left join @T2 b on a.tID=b.tID
where a.tEvent=@tEvent and b.tID is null or (
(b.tCount<80 or b.tCount>90)
and a.tID not in(select tID from @t2 where tCount between 80 and 90))
表还是T1,T2两个基本表
目标表如下:
TResult: tID tMin tMax
001 80 90
002 79 92
010 -1 -1
011 86 86
..................
输入条件:输入一个tEvent名称,如果,“上课”,根据T1,T2生成一张表,InputMin=80,InputMax=90统计条件:在T2中查找“上课”事件的所有ID,如果某个ID在T2中有记录如n条,(n>=1)
则该ID对应的tMin,tMax取值的方法为:
tMmin=取n条记录中abs(tCount-InputMin)最小的那个
tMax=取n条记录中abs(tCount-InputMax)最小的那个 如果某个ID在T2中没有记录,则tMin=-1,tMax=-1;
在以上的统计条件中tCount统计范围:[InputMin-10,InputMax+10]
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-@InputMin) in(select min(abs(tCount-@InputMin)) 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
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-@InputMin) in(select min(abs(tCount-@InputMin)) 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