求助一条超难的SQL语句(开发环境为:delphi7,sql2000,所用控件为ADOQuery)1、开发环境如标题中描述。
2、表结构:
字段:井号 时间 泵状态
查询条件:在(同一天中),(泵状态为‘开’)且(泵‘开’的次数大于2次)的(离现在最近一 天)的记录
3、说明:字段‘时间’中包含年、月、日、时、分;‘泵状态’为开或者关。
我实在是写不出来,求教各位兄弟姐妹了!在下先谢了!
2、表结构:
字段:井号 时间 泵状态
查询条件:在(同一天中),(泵状态为‘开’)且(泵‘开’的次数大于2次)的(离现在最近一 天)的记录
3、说明:字段‘时间’中包含年、月、日、时、分;‘泵状态’为开或者关。
我实在是写不出来,求教各位兄弟姐妹了!在下先谢了!
--建立测试数据
CREATE TABLE ctb([井号] int,[时间] datetime,[泵状态] char(10))INSERT ctb
SELECT 1,'2008-04-16 17:30:33','开'
UNION ALL SELECT 1,'2008-04-16 18:30:33','开'
UNION ALL SELECT 2,'2008-04-16 18:30:34','开'
UNION ALL SELECT 2,'2008-04-15 14:30:34','关'
UNION ALL SELECT 3,'2008-04-17 01:30:34','开'
UNION ALL SELECT 3,'2008-04-17 05:30:34','关'
UNION ALL SELECT 3,'2008-04-17 07:37:34','开'
UNION ALL SELECT 4,'2008-04-16 19:30:34','开'
/*
1 2008-04-16 17:30:33.000 开
1 2008-04-16 18:30:33.000 开
2 2008-04-16 18:30:34.000 开
2 2008-04-15 14:30:34.000 关
3 2008-04-17 01:30:34.000 开
3 2008-04-17 05:30:34.000 关
3 2008-04-17 07:37:34.000 开
4 2008-04-16 19:30:34.000 开 */
--查询结果
select * from (select [井号],[泵状态] from ctb
WHERE DATEPART(dd,getdate())-DATEPART(dd,[时间])=1
and DATEPART(year,[时间])=DATEPART(year,GETDATE())
GROUP BY [井号],[泵状态] HAVING sum(1)>=2) b,ctb a
where a.[井号]=b.[井号] and
DATEPART(dd,getdate())-DATEPART(dd,a.[时间])=1
and
DATEPART(year,a.[时间])=DATEPART(year,GETDATE())/*
1 开 1 2008-04-16 17:30:33.000 开
1 开 1 2008-04-16 18:30:33.000 开 */
可以显示大于两次的,上例连等于两次的一并显示
WHERE DATEPART(dd,getdate())-DATEPART(dd,[时间])=1
and DATEPART(year,[时间])=DATEPART(year,GETDATE())
GROUP BY [井号],[泵状态] HAVING sum(1)>=2) b,ctb a
where a.[井号]=b.[井号] and
DATEPART(dd,getdate())-DATEPART(dd,a.[时间])=1
and
DATEPART(year,a.[时间])=DATEPART(year,GETDATE())
select 井号 from 表 where 时间 = '日期' group by 井号 HAVING COUNT(状态) > 2 and sum(状态) >=2至于日期,你可以先在delphi里判断一下最近的日期
from (select 井号, 泵状态
from ctb
WHERE DATEPART(dd, getdate()) - DATEPART(dd, 时间) = 1
and DATEPART(year, 时间) = DATEPART(year, GETDATE())
and 泵状态 = '开'
GROUP BY 井号, 泵状态
HAVING sum(1) >= 2) b,
ctb a
where a. 井号 = b. 井号
and DATEPART(dd, getdate()) - DATEPART(dd, a. 时间) = 1
and DATEPART(year, a. 时间) = DATEPART(year, GETDATE())
and a. 泵状态 = '开'
select ctb.井号,max(ctb.时间) 时间,ctb.泵状态
from ctb
where exists
(
select * from
(select 井号,substring(convert(varchar(20),时间,20),1,10) 时间,count(*) 次数,泵状态 from ctb
where ctb.泵状态='开'
group by substring(convert(varchar(20),时间,20),1,10),井号,泵状态
having count(*)>1) as b
where ctb.井号=b.井号 and substring(convert(varchar(20),ctb.时间,20),1,10)=b.时间 and ctb.泵状态=b.泵状态)
group by ctb.井号,ctb.泵状态--结果
1 2008-04-16 18:30:33.000 开
3 2008-04-17 07:37:34.000 开
是要求这样的结果吧
应该是:select a.*
from (select 井号, 泵状态
from ctb
WHERE DATEPART(dd, getdate()) - DATEPART(dd, 时间)< = 1
and DATEPART(year, 时间) = DATEPART(year, GETDATE())
and DATEPART(mm, 时间) = DATEPART(mm, GETDATE())
and 泵状态 = '开'
GROUP BY 井号, 泵状态
HAVING sum(1) >= 2) b,
ctb a
where a. 井号 = b. 井号
and DATEPART(dd, getdate()) - DATEPART(dd, a. 时间) <= 1
and DATEPART(year, a. 时间) = DATEPART(year, GETDATE())
and DATEPART(mm, a.时间) = DATEPART(mm, GETDATE())
and a. 泵状态 = '开'/*
1 2008-04-16 17:30:33.000 开
1 2008-04-16 18:30:33.000 开
3 2008-04-17 01:30:34.000 开
3 2008-04-17 07:37:34.000 开 */
insert tb
select 'k01', '2008-04-11 12:12:12', '开' union all
select 'k02', '2008-04-12 13:12:12', '开' union all
select 'k01', '2008-04-13 14:12:12', '关' union all
select 'k02', '2008-04-14 14:52:12', '关' union all
select 'k01', '2008-04-16 15:22:12', '开' union all
select 'k02', '2008-04-16 15:12:12', '开' union all
select 'k02', '2008-04-16 15:23:12', '开' union all
select 'k02', '2008-04-16 15:45:12', '关' union all
select 'k01', '2008-04-16 17:35:12', '关' union all
select 'k01', '2008-04-16 18:12:12', '开' union all
select 'k01', '2008-04-16 19:22:12', '关' union all
select 'k01', '2008-04-16 19:12:12', '开' union all
select 'k01', '2008-04-16 20:12:12', '关' union all
select 'k01', '2008-04-16 21:12:12', '开' union all
select 'k01', '2008-04-17 08:12:12', '开' union all
select 'k01', '2008-04-17 09:12:12', '关' union all
select 'k01', '2008-04-17 09:12:12', '开'--统计昨天的,若包含今天的,改每件为:day(a.时间)>=day(getdate()-1)
select a.* from tb a,(select 井号, count(1) 次数 from tb group by 井号) b
where a.井号=b.井号 and a.泵状态='开' and day(a.时间)=day(getdate()-1) and b.次数>=2
order by a.时间
drop table tb/*
井号 时间 泵状态
---------- ------------------------------------------------------ ----
k02 2008-04-16 15:12:12.000 开
k01 2008-04-16 15:22:12.000 开
k02 2008-04-16 15:23:12.000 开
k01 2008-04-16 18:12:12.000 开
k01 2008-04-16 19:12:12.000 开
k01 2008-04-16 21:12:12.000 开(所影响的行数为 6 行)
*/