怎样判断出存在这样一条数据呢,各位帮帮我
/*
create table ta2(id varchar(4),facid varchar(8),machinenumber varchar(8),number int,repairvalue int,data datetime)
insert into ta2
select '01','aaa', '80#', 100, 120 ,'2005-08-15'
union all select '02','bbb', '90#', 100, 8 ,'2005-08-15'
union all select '03','ccc', '60#', 100, 90 ,'2005-08-15'
union all select '04','ddd', '40#', 100, 150 ,'2005-08-15'
union all select '05','eee', '50#', 100, 300 ,'2005-08-15'
union all select '06','aaa', '80#', 100, 140 ,'2005-08-16'
union all select '07','bbb', '90#', 100, 110 ,'2005-08-16'
union all select '08','fff', '60#', 100, 150 ,'2005-08-16'
union all select '09','ddd', '40#', 100, 80 ,'2005-08-16'
union all select '10','kkk', '50#', 100, 40 ,'2005-08-16'
*/
--drop table ta2
/*
查询2005-08-16
想生成如下结果
id facid machinenumber number repairvalue sign
06 aaa 80# 140 100 是
07 bbb 90# 110 100 否
08 fff 60# 150 100 否
条件是:如果存在与2005-08-16查询结果相同facid,machinenumber最近而且repairvalue>number*1.0时sign为是
*/
--查询2005-08-16的数据
select id,facid,machinenumber,repairvalue,number
from ta2 where repairvalue>number*1.0 and data='2005-08-16'
--相同facid,machinenumber最近而且repairvalue>number*1.0的数据
select b.id, A.facid,A.machinenumber,b.repairvalue,b.number
from ta2 A ,
(select max(id) id ,facid,machinenumber,repairvalue,number from ta2
where data<>'2005-08-16' and repairvalue>number*1.0
group by facid,machinenumber,repairvalue,number) B
where A.machinenumber=b.machinenumber and a.facid=b.facid and data='2005-08-16'
and a.repairvalue>a.number*1.0
--怎样判断出存在这样一条数据呢,各位帮帮我