号码A|号码B|过程id|开始时间|结束时间
AAA|BBB|0|16:02:27|16:02:27
AAA|BBB|1|16:02:27|16:02:27
AAA|BBB|2|16:02:27|16:02:43
AAA|BBB|3|16:02:43|16:02:56
AAA|BBB|4|16:02:56|16:03:17
AAA|CCC|0|16:07:15|16:07:15
AAA|CCC|1|16:07:15|16:07:15
AAA|CCC|2|16:07:15|16:08:54
以上是通过select语句选择出满足条件的纪录集,我希望通过T-SQL语句(或者存储过程),从中分检出两条记录:号码A|号码B|开始时间|结束时间
AAA|BBB|16:02:27|16:03:17
AAA|CCC|16:07:15|16:08:54分检原则:根据"过程id",从0开始,到下一个0出现之前结束,这一批的记录集合并为一条记录谢谢大家
AAA|BBB|0|16:02:27|16:02:27
AAA|BBB|1|16:02:27|16:02:27
AAA|BBB|2|16:02:27|16:02:43
AAA|BBB|3|16:02:43|16:02:56
AAA|BBB|4|16:02:56|16:03:17
AAA|CCC|0|16:07:15|16:07:15
AAA|CCC|1|16:07:15|16:07:15
AAA|CCC|2|16:07:15|16:08:54
以上是通过select语句选择出满足条件的纪录集,我希望通过T-SQL语句(或者存储过程),从中分检出两条记录:号码A|号码B|开始时间|结束时间
AAA|BBB|16:02:27|16:03:17
AAA|CCC|16:07:15|16:08:54分检原则:根据"过程id",从0开始,到下一个0出现之前结束,这一批的记录集合并为一条记录谢谢大家
--这样?
select 号码A,号码B,Min(开始时间) as 开始时间,Max(结束时间) AS 结束时间
from 表名
group by 号码A,号码B
insert into T select 'AAA','BBB','1','16:02:27','16:02:27'
insert into T select 'AAA','BBB','2','16:02:27','16:02:43'
insert into T select 'AAA','BBB','3','16:02:43','16:02:56'
insert into T select 'AAA','BBB','4','16:02:56','16:03:17'
insert into T select 'AAA','CCC','0','16:07:15','16:07:15'
insert into T select 'AAA','CCC','1','16:07:15','16:07:15'
insert into T select 'AAA','CCC','2','16:07:15','16:08:54'
--这样?
select 号码A,号码B,Min(开始时间) as 开始时间,Max(结束时间) AS 结束时间
from T
group by 号码A,号码B drop table T
/*结果:号码A 号码B 开始时间 结束时间
AAA BBB 16:02:27 16:03:17
AAA CCC 16:07:15 16:08:54
*/
from 表名
group by 号码A,号码B
select 号码A,号码B, 开始时间, 结束时间
from t a
where not exists(select 1 from t where a.号码A=号码A and 号码B<a.号码B)
insert into T select 'AAA','BBB','1','16:02:27','16:02:27'
insert into T select 'AAA','BBB','2','16:02:27','16:02:43'
insert into T select 'AAA','BBB','3','16:02:43','16:02:56'
insert into T select 'AAA','BBB','4','16:02:56','16:03:17'
insert into T select 'AAA','CCC','0','16:07:15','16:07:15'
insert into T select 'AAA','CCC','1','16:07:15','16:07:15'
insert into T select 'AAA','CCC','2','16:07:15','16:08:54'select * from tselect 号码A,号码B, 开始时间, 结束时间
from t a
where not exists(select 1 from t where a.号码A=号码A and 号码B=a.号码B and 过程id>a.过程id)
---------
AAA BBB 16:02:56 16:03:17
AAA CCC 16:07:15 16:08:54
------------------------------------------------------------------------------------
楼主如是说
上面的两位大侠没有用到ID 肯定不对啊~
AAA|BBB|1|16:02:27|16:02:27
AAA|BBB|2|16:02:27|16:02:43
AAA|BBB|3|16:02:43|16:02:56
AAA|BBB|4|16:02:56|16:03:17-----------
這5條全並一條記錄,AAA|CCC|0|16:07:15|16:07:15
AAA|CCC|1|16:07:15|16:07:15
AAA|CCC|2|16:07:15|16:08:54
---------
這3條合並一條記錄我是用 過程id 來判斷啊
insert into T select 'AAA','BBB','1','16:02:27','16:02:27'
insert into T select 'AAA','BBB','2','16:02:27','16:02:43'
insert into T select 'AAA','BBB','3','16:02:43','16:02:56'
insert into T select 'AAA','BBB','4','16:02:56','16:03:17'
insert into T select 'AAA','CCC','0','16:07:15','16:07:15'
insert into T select 'AAA','CCC','1','16:07:15','16:07:15'
insert into T select 'AAA','CCC','2','17:07:15','17:08:54'
insert into T select 'AAA','BBB','0','17:02:27','17:02:27'
insert into T select 'AAA','BBB','1','17:02:27','17:02:27'select * from tselect 号码A,号码B, 开始时间, 结束时间
from t a
where not exists(select 1 from t where a.号码A=号码A and 号码B=a.号码B and 过程id>a.过程id)drop table Tfa_ge(鶴嘯九天) 测试一下上边的数据
insert into T select 'AAA','BBB','1','16:02:27','16:02:27'
insert into T select 'AAA','BBB','2','16:02:27','16:02:43'
insert into T select 'AAA','BBB','3','16:02:43','16:02:56'
insert into T select 'AAA','BBB','4','16:02:56','16:03:17'
insert into T select 'AAA','CCC','0','16:07:15','16:07:15'
insert into T select 'AAA','CCC','1','16:07:15','16:07:15'
insert into T select 'AAA','CCC','2','16:07:15','16:08:54'
insert into T select 'AAA','BBB','0','16:09:27','16:09:43'
insert into T select 'AAA','BBB','1','16:09:43','16:09:56'
insert into T select 'AAA','BBB','2','16:10:56','16:10:17'select *,tmp=0 into # from Tdeclare @a varchar(100),@b varchar(100),@id varchar(100),@tmp int
update #
set @tmp=case when A=@a and B=@b
then @tmp
else isnull(@tmp,0)+1 end,
@a=a,
@b=b,
tmp=@tmpselect A,B,min(be) as be,max(en) as en from #
group by A,B,tmp
order by tmp/*
A B be en
----------------------------------------
AAA BBB 16:02:27 16:03:17
AAA CCC 16:07:15 16:08:54
AAA BBB 16:09:27 16:10:17*/drop table #
drop table t
insert into T select 'AAA','BBB','1','16:02:27','16:02:27'
insert into T select 'AAA','CCC','2','16:02:27','16:02:43'
insert into T select 'AAA','BBB','3','16:02:43','16:02:56'
insert into T select 'AAA','BBB','4','16:02:56','16:03:17'
insert into T select 'AAA','CCC','0','16:07:15','16:07:15'
insert into T select 'AAA','BBB','1','16:07:15','16:07:15'
insert into T select 'AAA','CCC','2','16:07:15','16:08:54'
insert into T select 'AAA','BBB','0','16:09:27','16:09:43'
insert into T select 'AAA','CCC','1','16:09:43','16:09:56'
insert into T select 'AAA','BBB','2','16:10:56','16:10:17'
lost_queen(初学后进) ( )
-----------------------------------
應該不會是這樣的數據,否則A字段 B字段(放哪個?)
AAA BBB or CCC ?
insert into T select 'AAA','BBB','1','16:02:27','16:02:27'
insert into T select 'AAA','CCC','2','16:02:27','16:02:43'
insert into T select 'AAA','BBB','3','16:02:43','16:02:56'
insert into T select 'AAA','BBB','4','16:02:56','16:03:17'
insert into T select 'AAA','CCC','0','16:07:15','16:07:15'
insert into T select 'AAA','BBB','1','16:07:15','16:07:15'
insert into T select 'AAA','CCC','2','16:07:15','16:08:54'
insert into T select 'AAA','BBB','0','16:09:27','16:09:43'
insert into T select 'AAA','CCC','1','16:09:43','16:09:56'
insert into T select 'AAA','BBB','2','16:10:56','16:10:17'
select *,tmp=0 into # from Tdeclare @a varchar(100),@b varchar(100),@id varchar(100),@tmp int
update #
set @tmp=case when id>@id
then @tmp
else isnull(@tmp,0)+1 end,
@id=id,
tmp=@tmp--select * from #select min(A) as A,min(B) as B,min(be) as be,max(en) as en from #
group by tmp
order by tmp
/*
AAA BBB 16:02:27 16:03:17
AAA BBB 16:07:15 16:08:54
AAA BBB 16:09:27 16:10:17*/drop table #
drop table t
---创建存储过程
Create Proc Pro_T_Test
As
Begin
Declare @T Table(Tmp Int,号码A Varchar(10),号码B Varchar(10),
过程id int,开始时间 Varchar(10),结束时间 Varchar(10))
Insert @T Select 0,号码A,号码B,过程id,开始时间,结束时间 From TTest --TTest为你的表名
Declare @号码A varchar(10),@号码B varchar(10),@Tmp int
---更新临时表
Update
@T
Set @Tmp=Case When 号码A=@号码A And 号码B=@号码B Then @Tmp
Else Isnull(@Tmp,0)+1 End,
@号码A=号码A,
@号码B=号码B,
Tmp=@Tmp
---查询结果
Select 号码A,
号码B,
Min(开始时间) As 开始时间,
Max(结束时间) As 结束时间
From
@T
Group By Tmp,号码A,号码B
Order By Tmp
End
Go
---调用存储过程
EXEC Pro_T_Test
号码A|号码B|过程id|开始时间|结束时间
AAA|BBB|0|16:02:27|16:02:27
AAA|BBB|1|16:02:27|16:02:27
AAA|BBB|2|16:02:27|16:02:43
AAA|BBB|3|16:02:43|16:02:56
AAA|BBB|4|16:02:56|16:03:17
AAA|CCC|0|16:07:15|16:07:15
AAA|CCC|1|16:07:15|16:07:15
AAA|CCC|2|16:07:15|16:08:54
AAA|DDD|1|16:07:15|16:07:15
AAA|DDD|2|16:07:15|16:07:15
AAA|DDD|3|16:07:15|16:08:54
这样的数据楼主期望出什么结果?是不是
AAABBB16:02:5616:03:17
AAACCC16:07:1516:08:54?
加入本群条件,至少对mssql使用1年以上,不满足条件都勿加
谢谢合作!
if exists(select name from sysobjects where name='test'and type='U')
drop table test
go
create table test(A varchar(20),B varchar(20),id int,starttime char(8),endtime char(8))
go
insert into test
select 'AAA','BBB',0,'16:00:00','16:00:01'
union all select 'AAA','BBB',1,'16:01:00','16:01:01'
union all select 'AAA','BBB',2,'16:02:00','16:02:01'
union all select 'AAA','BBB',3,'16:03:00','16:03:01'
union all select 'AAA','CCC',0,'16:04:00','16:04:01'
union all select 'AAA','CCC',1,'16:05:00','16:05:01'
union all select 'AAA','CCC',2,'16:06:00','16:06:01'
union all select 'AAA','CCC',3,'16:07:00','16:07:01'
union all select 'AAA','BBB',0,'16:08:00','16:08:01'
union all select 'AAA','BBB',1,'16:09:00','16:09:01'
union all select 'AAA','BBB',2,'16:10:00','16:10:01'
union all select 'AAA','CCC',0,'16:11:00','16:11:01'
union all select 'AAA','CCC',1,'16:12:00','16:12:01'
union all select 'AAA','CCC',2,'16:13:00','16:13:01'
union all select 'AAA','CCC',3,'16:14:00','16:14:01'
go
if exists(select name from sysobjects where name='fn_test'and type='FN')
drop function fn_test
go
create function fn_test
(
@pA varchar(20),
@pB varchar(20),
@pStarttime char(8)
)
returns char(8)
as
begin
declare @pEndtime char(8)
declare @vNexttime char(8)
if exists(select 1 from test where A=@pA and B=@pB and id=0 and starttime>@pStarttime)
begin
select top 1 @vNexttime=starttime from test
where A=@pA and B=@pB and id=0 and starttime>@pStarttime
order by starttime
select @pEndtime=max(endtime)from test where A=@pA and B=@pB and endtime<@vNexttime
end
else
begin
select @pEndtime=max(endtime)from test where A=@pA and B=@pB
end
return @pEndtime
end
go
select a.A,a.B,a.starttime,dbo.fn_test(a.A,a.B,a.starttime)as endtime
from test a
where a.id=0
order by a.A,a.B,a.starttime
/*
A B starttime endtime
-------------------- -------------------- --------- --------
AAA BBB 16:00:00 16:03:01
AAA BBB 16:08:00 16:10:01
AAA CCC 16:04:00 16:07:01
AAA CCC 16:11:00 16:14:01(4 row(s) affected)
*/