表格如下:id time num
a 9:00 22
a 10:00 2
a 11:00 20
a 12:00 9
a 13:00 18
.. . ......a 16:00 15b 9:00 22
b 10:00 2
b 11:00 20
b 12:00 9
b 13:00 18
.. . ......b 16:00 14c 9:00 11
.. . ......
.. . ......
.. . ......
.. . ......每小时测量一次数据
a 和 b 在 9:00--15:00 期间的每一个数字完全相同,但是16:00以后就不相同了,如何找出 9:00/ 16:00这两个时间点?
a 9:00 22
a 10:00 2
a 11:00 20
a 12:00 9
a 13:00 18
.. . ......a 16:00 15b 9:00 22
b 10:00 2
b 11:00 20
b 12:00 9
b 13:00 18
.. . ......b 16:00 14c 9:00 11
.. . ......
.. . ......
.. . ......
.. . ......每小时测量一次数据
a 和 b 在 9:00--15:00 期间的每一个数字完全相同,但是16:00以后就不相同了,如何找出 9:00/ 16:00这两个时间点?
if object_id('tb') is not null
drop table tb
go
create table tb
(
id varchar(10),
time datetime,
num int
)
go
insert into tb
select 'a','9:00',22 union all
select 'a','10:00',2 union all
select 'a','11:00',20 union all
select 'a','12:00',9 union all
select 'a','13:00',18 union all
select 'a','14:00',15 union all
select 'a','15:00',14 union all
select 'a','16:00',12 union all
select 'a','17:00',10 union all
select 'a','18:00',9 union allselect 'b','9:00',22 union all
select 'b','10:00',2 union all
select 'b','11:00',20 union all
select 'b','12:00',9 union all
select 'b','13:00',18 union all
select 'b','14:00',15 union all
select 'b','15:00',14 union all
select 'b','16:00',10 union all
select 'b','17:00',19 union all
select 'b','18:00',30
go
select convert(varchar(5),a1.time,108),convert(varchar(5),a2.time,108) from
(
select top 1 t1.time from (select * from tb where id='a') t1 inner join (select * from tb where id='b') t2 on t1.time=t2.time and t1.num=t2.num order by time
)a1
cross apply
(
select top 1 t1.time from (select * from tb where id='a') t1 inner join (select * from tb where id='b') t2 on t1.time=t2.time and t1.num<>t2.num order by time
)a2
/*
----- -----
09:00 16:00(1 行受影响)*/
就是一条语句 ,比较某段时间内两个id 的num 完全相同 吧?
CREATE TABLE [dbo].[T_ceshi] (
[id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[time1] [varchar] (5) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[num] [int] NOT NULL
) ON [PRIMARY]
--插入上述数据
--SQL CODE
SELECT time1, num
FROM T_ceshi where id in('a','b')
group by time1,num
having count(time1)=2
order by time1