# 怎样判断出存在这样一条数据呢，各位帮帮我

geniusqing 2005-08-16 06:13:07
/*
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
/*

id facid machinenumber number repairvalue sign
06 aaa 80# 140 100 是
07 bbb 90# 110 100 否
08 fff 60# 150 100 否

*/
--查询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

--怎样判断出存在这样一条数据呢，各位帮帮我
...全文
138 点赞 收藏 13

13 条回复

select t3.id,t3.facid,t3.machinenumber,t3.repairvalue,t3.number,case when isnull(sid,'否')='否' then '否' else '是' end as sign
from
(select id,facid,machinenumber,repairvalue,number,
sid=(select max(id) as sid from ta2 where facid=t1.facid and machinenumber=t1.machinenumber and data<>t1.data and repairvalue>number*1.0)
from ta2 t1 where repairvalue>number*1.0 and data='2005-08-16'
)t3

select t3.id,t3.facid,t3.machinenumber,t3.repairvalue,t3.number,case when t2.repairvalue>t2.number*1.0 then '是' else '否' end as sign
from
(select id,facid,machinenumber,repairvalue,number,
sid=(select max(id) from ta2 where facid=t1.facid and machinenumber=t1.machinenumber and data<>t1.data)
from ta2 t1 where repairvalue>number*1.0 and data='2005-08-16'
)t3 left join ta2 t2 on t3.sid=t2.id

geniusqing 2005-08-17

geniusqing 2005-08-17

id facid machinenumber number repairvalue sign
---- -------- ------------- ----------- ----------- ----
06 aaa 80# 100 140 是
07 bbb 90# 100 110 否
08 fff 60# 100 150 否

--查询2005-08-16的数据
select id,facid,machinenumber,repairvalue,number
from ta2 where repairvalue>number*1.0 and data='2005-08-16'

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

xueguang 2005-08-17
--照你的思路写,不过把data<>'2005-08-16'改为data='2005-08-16',这样好像更满足你的意思
select id,facid,machinenumber,number,repairvalue,case when exists(
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

) then '是' else '否' end sign from ta2 where repairvalue>number*1.0 and data='2005-08-16'

--结果
id facid machinenumber number repairvalue sign
---- -------- ------------- ----------- ----------- ----
06 aaa 80# 100 140 是
07 bbb 90# 100 110 是
08 fff 60# 100 150 是

（所影响的行数为 3 行）

dufeng16 2005-08-17

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为是”

geniusqing 2005-08-17

--查询2005-08-16的数据
select id,facid,machinenumber,repairvalue,number
from ta2 where repairvalue>number*1.0 and data='2005-08-16'

--与2005-08-16相同facid,machinenumber而且repairvalue>number*1.0的最大id的数据

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
--如果存在的话，sign为是，否则为否

geniusqing 2005-08-17

xueguang 2005-08-16
--写的麻烦点
select id,facid,machinenumber,number,repairvalue,case when facid+machinenumber+convert(char(10),data,102) in(
select facid+max(machinenumber)+max(convert(char(10),data,102)) from ta2 where convert(char(10),data,102)='2005.08.16' group by facid) and repairvalue>number*1.0 then '是' else '否' end sign from ta2 where
facid+machinenumber+convert(char(10),data,102) in(
select facid+max(machinenumber)+max(convert(char(10),data,102)) from ta2 where convert(char(10),data,102)='2005.08.16' group by facid) and repairvalue>number*1.0

--结果
id facid machinenumber number repairvalue sign
---- -------- ------------- ----------- ----------- ----
06 aaa 80# 100 140 是
07 bbb 90# 100 110 是
08 fff 60# 100 150 是

（所影响的行数为 3 行）

2.7w+

MS-SQL Server 应用实例

2005-08-16 06:13