补充一下:上面的例子假设有a、b、c三个站点,a站点有2个车道,b站点有3个车道,c站点有4个车道
解决方案 »
- 各位帮我解决这个问题?
- 求一个alter table的sql语句
- 如何删除所有以"usp_"为前缀的存储过程,用SQL语句
- 求一 sql 语句:select count(P_ID) from Company 的同时,我还想读取另几个字段(比如:company_name,company_address)这样的语句应该
- 一个比较有难度得SQL语句得问题,高手进来拿分啦
- for each row语句不能用??
- 问select一个bigint,如果转换16进制字符输出?
- 时间错误问题,急!
- 请教高手有关数组的问题
- 如何写文中的SQL语句
- 求以复杂SQL语句 !
- 怎么产生一个30-50或者 30-60的随机数!
为 a站点有2个车道,b站点有2个车道,c站点有3个车道
就说明b、c有误?你是不是这个意思哦?如果是就好办了,把数先count出来,有问题的再去找逐条时间相减不为5分钟的,然后记录相减不为5的两个时间明白?一定要写sql?
create table tb(时间 datetime , 金额 int)
insert into tb values('2007-1-1 10:00:23' , 8 )
insert into tb values('2007-1-1 10:01:24' , 4 )
insert into tb values('2007-1-1 10:05:00' , 2 )
insert into tb values('2007-1-1 10:06:12' , 3 )
insert into tb values('2007-1-1 10:08:00' , 1 )
insert into tb values('2007-1-1 10:12:11' , 5 )
go--时间段>=10:00:00 and 时间段<10:05:00
select dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,-1,时间),120),dateadd(ss,-1,时间))/5)*5,convert(varchar(10),时间,120)) as 时间段,
count(*) as 行数,
sum(金额) as 总金额
from tb
group by dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,-1,时间),120),dateadd(ss,-1,时间))/5)*5,convert(varchar(10),时间,120))
/*
时间段 行数 总金额
------------------------------------------------------ ----------- -----------
2007-01-01 10:00:00.000 3 14
2007-01-01 10:05:00.000 2 4
2007-01-01 10:10:00.000 1 5
(所影响的行数为 3 行)
*/--时间段>10:00:00 and 时间段<=10:05:00
select dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,1,时间),120),dateadd(ss,1,时间))/5)*5,convert(varchar(10),时间,120)) as 时间段,
count(*) as 行数,
sum(金额) as 总金额
from tb
group by dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,1,时间),120),dateadd(ss,1,时间))/5)*5,convert(varchar(10),时间,120))
/*
时间段 行数 总金额
------------------------------------------------------ ----------- -----------
2007-01-01 10:00:00.000 2 12
2007-01-01 10:05:00.000 3 6
2007-01-01 10:10:00.000 1 5(所影响的行数为 3 行)
*/drop table tb
from TrafficTable
group by 站点, 车道 having count(*)%288<>0不过这里还有个风险就是上传的数据多了而不是缺了,那么也可能满足288倍数,就要去掉having子句,把数列出来除以每个站点、每个车道应该出现的记录数来检查。
insert into @t select'a', 1, '2008-04-17 00:00:00', 20 union select
'a', 2, '2008-04-17 00:00:00', 34 union select
'b', 1, '2008-04-17 00:00:00', 45 union select
'b', 2, '2008-04-17 00:00:00', 86 union select
'c', 1, '2008-04-17 00:00:00', 78 union select
'c', 2, '2008-04-17 00:00:00', 45 union select
'c', 3, '2008-04-17 00:00:00', 76 union select
'c', 4, '2008-04-17 00:00:00', 36 union select
'a', 1, '2008-04-17 00:05:00', 10 union select
'a', 2, '2008-04-17 00:05:00', 14 union select
'b', 1, '2008-04-17 00:05:00', 15 union select
'b', 2, '2008-04-17 00:05:00', 16 union select
'b', 3, '2008-04-17 00:05:00', 12 union select
'c', 1, '2008-04-17 00:05:00', 18 union select
'c', 2, '2008-04-17 00:05:00', 15 union select
'c', 3, '2008-04-17 00:05:00', 16
select b.站点编号,b.上传时间 as 丢失数据时间 from(select 站点编号,max(cnt) as cnt from (select 站点编号,上传时间,count(车道编号)as cnt from @t group by 站点编号,上传时间) t
group by 站点编号) a,(select 站点编号,上传时间,count(车道编号)as cnt from @t group by 站点编号,上传时间) bwhere a.站点编号=b.站点编号 and a.cnt<>b.cntb 在 '2008-04-17 00:00:00' 丢失了一行
c 在 '2008-04-17 00:05:00' 丢失了一行
这是今天实际的查询结果:
use trafficdb
select siteid, cd, count(*)
from History200804 where (datatime>='2008-04-16 00:00:00' and datatime<'2008-04-17 00:00:00')
group by siteid, cd having count(*)%288 <>01 1 205
3 3 276
3 1 276
1 3 205
1 5 205
1 2 205
3 2 276
1 4 205
3 4 276
1 6 205
有10个车道都缺少数据,都不只缺少一条数据,那么再回去找的话还是不好找啊 ,还要group by 站点,车道,把以站点、车道为单位的当天数据全部调出来再一条一条的找 是这样吧?
CREATE TABLE TrafficTable(站点编号 VARCHAR(10),车道编号 INT,上传时间 DATETIME,数据值 INT)
GOINSERT INTO TrafficTable
SELECT 'a',1,'2008-04-17 00:00:00',20 UNION ALL
SELECT 'a',2,'2008-04-17 00:00:00',34 UNION ALL
SELECT 'b',1,'2008-04-17 00:00:00',45 UNION ALL
SELECT 'b',2,'2008-04-17 00:00:00',86 UNION ALL
SELECT 'b',3,'2008-04-17 00:00:00',12 UNION ALL
SELECT 'c',1,'2008-04-17 00:00:00',78 UNION ALL
SELECT 'c',2,'2008-04-17 00:00:00',45 UNION ALL
SELECT 'c',3,'2008-04-17 00:00:00',76 UNION ALL
SELECT 'c',4,'2008-04-17 00:00:00',36 UNION ALL
SELECT 'a',1,'2008-04-17 00:05:00',10 UNION ALL
SELECT 'a',2,'2008-04-17 00:05:00',14 UNION ALL
SELECT 'b',1,'2008-04-17 00:05:00',15 UNION ALL
SELECT 'b',2,'2008-04-17 00:05:00',16 UNION ALL
SELECT 'b',3,'2008-04-17 00:05:00',12 UNION ALL
SELECT 'c',1,'2008-04-17 00:05:00',18 UNION ALL
SELECT 'c',2,'2008-04-17 00:05:00',15 UNION ALL
SELECT 'c',3,'2008-04-17 00:05:00',16 UNION ALL
SELECT 'c',4,'2008-04-17 00:05:00',16
GO--应该有个表保存站点和车道的表吧
CREATE TABLE Station(站点编号 VARCHAR(10),车道编号 INT)
GO
INSERT INTO Station
SELECT 'a',1 UNION ALL
SELECT 'a',2 UNION ALL
SELECT 'b',1 UNION ALL
SELECT 'b',2 UNION ALL
SELECT 'b',3 UNION ALL
SELECT 'c',1 UNION ALL
SELECT 'c',2 UNION ALL
SELECT 'c',3 UNION ALL
SELECT 'c',4
GODECLARE @begin_time DATETIME
DECLARE @end_time DATETIMESET @begin_time = '2008-04-17 00:00:00' --你要检查的开始时间
SET @end_time = '2008-04-17 00:10:00' --你要检查的开始时间DECLARE @temp_time TABLE (时间 DATETIME)
WHILE @begin_time<=@end_time
BEGIN
INSERT INTO @temp_time(时间) VALUES(@begin_time)
SET @begin_time = DATEADD(minute,5,@begin_time)
ENDSELECT a.站点编号
,a.车道编号
,b.上传时间
,b.数据值
FROM (SELECT a.站点编号
,a.车道编号
,b.时间
FROM Station a
CROSS JOIN @temp_time b)a
LEFT JOIN TrafficTable b ON a.站点编号 = b.站点编号 AND a.车道编号 = b.车道编号 AND DATEDIFF(second,a.时间,b.上传时间)>=0 AND DATEDIFF(second,a.时间,b.上传时间)<300-- 那些为NULL的值就是缺少数据的值.
(SELECT a.站点编号
,a.车道编号
,b.时间
FROM Station a
CROSS JOIN @temp_time b)a
然后与实践的记录left join就得到缺少的数据.如果刚好是5分钟,那么DATEDIFF(second,a.时间,b.上传时间)>=0 AND DATEDIFF(second,a.时间,b.上传时间)<300 可以改为a.时间 = b.上传时间
DECLARE @begin_time DATETIME
DECLARE @end_time DATETIMESET @begin_time = '2008-04-17 00:00:00' --你要检查的开始时间
SET @end_time = '2008-04-17 00:10:00' --你要检查的开始时间DECLARE @temp_time TABLE (时间 DATETIME)
WHILE @begin_time<=@end_time
BEGIN
INSERT INTO @temp_time(时间) VALUES(@begin_time)
SET @begin_time = DATEADD(minute,5,@begin_time)
ENDSELECT *
FROM
(SELECT a.站点编号
,a.车道编号
,b.上传时间
,b.数据值
FROM (SELECT a.站点编号
,a.车道编号
,b.时间
FROM Station a
CROSS JOIN @temp_time b)a
LEFT JOIN TrafficTable b ON a.站点编号 = b.站点编号 AND a.车道编号 = b.车道编号 AND DATEDIFF(second,a.时间,b.上传时间)>=0 AND DATEDIFF(second,a.时间,b.上传时间)<300
)X
WHERE 上传时间 IS NULL
SELECT *
FROM
(SELECT a.站点编号
,a.车道编号
,a.时间 as 应该上传时间
,b.上传时间
,b.数据值
FROM (SELECT a.站点编号
,a.车道编号
,b.时间
FROM Station a
CROSS JOIN @temp_time b)a
LEFT JOIN TrafficTable b ON a.站点编号 = b.站点编号 AND a.车道编号 = b.车道编号 AND DATEDIFF(second,a.时间,b.上传时间)>=0 AND DATEDIFF(second,a.时间,b.上传时间)<300
)X
WHERE 上传时间 IS NULL