求一sql语句:如何得到查询某天所有站号的报警次数和报警持续时间!
表A 的字段定义如下:
站号 类型代码 监测时间 监测值
varchar(10) varchar(10) datetime float
01A01 01001 2007-1-10 10:20:00 2.24
01A02 01002 2007-1-10 10:20:00 13
02A01 01003 2007-1-10 10:20:00 6.57
02A02 01004 2007-1-10 10:20:00 3.59
03A01 01002 2007-1-10 10:20:00 16
03A04 01003 2007-1-10 10:20:00 5.87
.
.
A中的记录是随着监测时间的增加而按照每个站号而增加的.
表B 的字段定义如下:
站号 报警开始时间 报警结束时间
varchar(10) datetime datetime
01A01 2007-1-10 10:20:00 2007-1-10 10:30:00
01A02 2007-1-10 10:10:00 2007-1-10 10:20:00
02A02 2007-1-10 10:10:00 2007-1-10 10:15:00
03A01 2007-1-10 10:10:00 2007-1-10 10:15:00
03A01 2007-1-10 10:20:00 2007-1-10 10:30:00
03A04 2007-2-12 10:20:00 2007-2-12 10:25:00
.
.
B: 其中并不是每个站号每天都有报警记录,有的有,有的没有.
想要根据要查询的天数来确定所有站号的报警次数和报警持续时间,如果有的站号当天没有报警记录,就记为0.
如查询 2007/01/10 这一天的记录如下:
站号 报警次数 报警持续时间
01A01 1 00:10:00
01A02 1 00:10:00
02A01 0 00:00:00 (当天没有报警记录的情况)
02A02 1 00:05:00
03A01 2 00:15:00
03A04 0 00:00:00 (当天没有报警记录的情况,在后天有)
[解决办法]
Create Table A
(站号varchar(10),
类型代码varchar(10),
监测时间datetime,
监测值float)
Insert A Select '01A01 ', '01001 ', '2007-1-10 10:20:00 ', 2.24
Union All Select '01A02 ', '01002 ', '2007-1-10 10:20:00 ', 13
Union All Select '02A01 ', '01003 ', '2007-1-10 10:20:00 ', 6.57
Union All Select '02A02 ', '01004 ', '2007-1-10 10:20:00 ', 3.59
Union All Select '03A01 ', '01002 ', '2007-1-10 10:20:00 ', 16
Union All Select '03A04 ', '01003 ', '2007-1-10 10:20:00 ', 5.87
Union All Select '01A01 ', '01001 ', '2007-1-10 10:25:00 ', 2.25
Union All Select '01A02 ', '01002 ', '2007-1-10 10:25:00 ', 14
Union All Select '02A01 ', '01003 ', '2007-1-10 10:25:00 ', 6.58
Union All Select '02A02 ', '01004 ', '2007-1-10 10:25:00 ', 3.56
Union All Select '03A01 ', '01002 ', '2007-1-10 10:25:00 ', 17
Union All Select '03A04 ', '01003 ', '2007-1-10 10:25:00 ', 5.12
Create Table B
(站号varchar(10),
报警开始时间datetime,
报警结束时间 datetime)
Insert B Select '01A01 ', '2007-1-10 10:20:00 ', '2007-1-10 10:30:00 '
Union All Select '01A02 ', '2007-1-10 10:10:00 ', '2007-1-10 10:20:00 '
Union All Select '02A02 ', '2007-1-10 10:10:00 ', '2007-1-10 10:15:00 '
Union All Select '03A01 ', '2007-1-10 10:10:00 ', '2007-1-10 10:15:00 '
Union All Select '03A01 ', '2007-1-10 10:20:00 ', '2007-1-10 10:30:00 '
Union All Select '03A04 ', '2007-2-12 10:20:00 ', '2007-2-12 10:25:00 '
GO
Select
站号,
报警次数,
Right(100 + 报警持续时间 / 60, 2) + ': ' + Right(100 + 报警持续时间 % 60, 2) + ':00 ' As 报警持续时间
From
(
Select
A.站号,
Count(B.站号) As 报警次数,
IsNull(SUM(DateDiff(mi, B.报警开始时间, B.报警结束时间)), 0) As 报警持续时间
From
(Select Distinct 站号, Convert(Varchar(10), 监测时间, 120) As 监测时间 From A) A
Left Join
B
On A.站号 = B.站号 And DateDiff(dd, B.报警开始时间, A.监测时间) = 0 And DateDiff(dd, B.报警结束时间, A.监测时间) = 0
Where DateDiff(dd, A.监测时间, '2007-1-10 ') = 0
Group By A.站号
) T
Order By 站号
GO
Drop Table A, B
--Result
/*
站号报警次数报警持续时间
01A01100:10:00
01A02100:10:00
02A01000:00:00
02A02100:05:00
03A01200:15:00
03A04000:00:00
*/
