时长，间隔，求次数算法，急急急！！！

Question

表结构和数据：
表1
DECLARE @TempaTab TABLE
(
    [ID]  INT  IDENTITY(1, 1)  PRIMARY KEY,
    [ADID] varchar(20),                     //编码
    [StartTime] varchar(19),                //开始时间
    [EndTime] varchar(19)                   //结束时间
)
INSERT INTO @TempaTab(ADID,StartTime,EndTime)
VALUES ('123','2003-01-01 03:44:29','2003-01-01 03:44:38')
表2
DECLARE @TempcTab TABLE
(
    [ID]  INT  IDENTITY(1, 1)  PRIMARY KEY,
    [ADID] varchar(20),                     //编码
    [PID] INT,                              //编号
    [IntervalTimes] INT                     //间隔时间(单位:秒)
)
INSERT INTO  @TempcTab(ADID,PID,IntervalTimes)
VALUES('123',1,4)
INSERT INTO  @TempcTab(ADID,PID,IntervalTimes)
VALUES('123',2,4)
INSERT INTO  @TempcTab(ADID,PID,IntervalTimes)
VALUES('123',3,4)问题：
根据表1的开始时间和结束时间能得到时间差为9秒
SELECT  datediff(second, StartTime, EndTime) FROM @TempaTab
现在要求：对应表1中的编码‘123’，表2中的3条数据根据间隔时间各出现了几次，3条数据的间隔时间是一样滴结果：因时长是9秒，那应该是对应的PID编号1,2,3各出现了一次，如果是8秒那对应的PID编号1,2出了一次，3木有出现

Answer 1

declare @ADID varchar(20)='123'
declare @IT int 
declare @I int =0
declare @J int =0
declare @G int =1
select @IT=DATEDIFF(s,starttime,endtime) from @TempaTab where ADID=@ADID
declare @TB table(ID int identity(1,1),PID int,IntervalTimes int,total int default 0)
insert into @TB(PID,IntervalTimes) select PID,IntervalTimes from @TempcTab where ADID=@ADID order by pid 
select @J=MAX(ID) from @TB
while @I<@IT
begin

select @I=@I+IntervalTimes from @TB where ID=@G
update @TB set total=total+1 where ID=@G
set @G=@G+1
if @G>@J 
set @G=1
end
select pid,total from @TB 

Answer 2

写的很繁琐，自己优化
DECLARE @TempaTab TABLE
(
   [ID] INT IDENTITY(1, 1) PRIMARY KEY,
   [ADID] varchar(20), --//编码
   [StartTime] varchar(19), --//开始时间
   [EndTime] varchar(19) --//结束时间
)
INSERT INTO @TempaTab(ADID,StartTime,EndTime)
VALUES ('123','2003-01-01 03:44:29','2003-01-01 03:44:37')DECLARE @TempcTab TABLE
(
   [ID] INT IDENTITY(1, 1) PRIMARY KEY,
   [ADID] varchar(20), --//编码
   [PID] INT, --//编号
   [IntervalTimes] INT --//间隔时间(单位:秒)
)
INSERT INTO @TempcTab(ADID,PID,IntervalTimes)
VALUES('123',1,4)
INSERT INTO @TempcTab(ADID,PID,IntervalTimes)
VALUES('123',2,4)
INSERT INTO @TempcTab(ADID,PID,IntervalTimes)
VALUES('123',3,4)select *,
case when (select datediff(ss,StartTime,EndTime) from @TempaTab where ADID=a.ADID)%
            (select sum(IntervalTimes) from @TempcTab 
                                          where ADID=a.ADID)
             > isnull((select sum(IntervalTimes) from @TempcTab 
                                          where ADID=a.ADID and PID<a.PID),0) 
             and 
             (select datediff(ss,StartTime,EndTime) from @TempaTab where ADID=a.ADID)%
            (select sum(IntervalTimes) from @TempcTab 
                                          where ADID=a.ADID)
             <=(select sum(IntervalTimes) from @TempcTab 
                                          where ADID=a.ADID) then  
            (select datediff(ss,StartTime,EndTime) from @TempaTab where ADID=a.ADID)/
            (select sum(IntervalTimes) from @TempcTab 
                                          where ADID=a.ADID)+1 else 
            (select datediff(ss,StartTime,EndTime) from @TempaTab where ADID=a.ADID)/
            (select sum(IntervalTimes) from @TempcTab 
                                          where ADID=a.ADID) end                              
                                         
 from  @TempcTab a
 
 /*
 ID          ADID                 PID         IntervalTimes 
----------- -------------------- ----------- ------------- -----------
1           123                  1           4             1
2           123                  2           4             1
3           123                  3           4             0(3 row(s) affected)

Answer 3

DECLARE @TempaTab TABLE
(
  [ID] INT IDENTITY(1, 1) PRIMARY KEY,
  [ADID] varchar(20), --编码
  [StartTime] varchar(19), --开始时间
  [EndTime] varchar(19) --结束时间
)
INSERT INTO @TempaTab(ADID,StartTime,EndTime)
VALUES ('123','2003-01-01 03:44:29','2003-01-01 03:44:38')
--表2
DECLARE @TempcTab TABLE
(
  [ID] INT IDENTITY(1, 1) PRIMARY KEY,
  [ADID] varchar(20), --编码
  [PID] INT, --编号
  [IntervalTimes] INT --间隔时间(单位:秒)
)
INSERT INTO @TempcTab(ADID,PID,IntervalTimes)
VALUES('123',1,4)
INSERT INTO @TempcTab(ADID,PID,IntervalTimes)
VALUES('123',2,4)
INSERT INTO @TempcTab(ADID,PID,IntervalTimes)
VALUES('123',3,4)
DECLARE @cnt INT 
SELECT @cnt =COUNT(*) FROM @TempcTab a JOIN @TempaTab b ON a.ADID=b.ADID
--SELECT @cnt
;WITH cte AS
(
SELECT a.id,b.ADID,b.PID,IntervalTimes,a.StartTime,EndTime,row_id=ROW_NUMBER()OVER( ORDER BY number,pid) 
FROM @TempaTab a JOIN @TempcTab b
ON a.ADID=b.ADID
CROSS APPLY (
SELECT number FROM master..spt_values WHERE type='p' AND number <=DATEDIFF(second, StartTime, EndTime)/IntervalTimes/@cnt
) app
)
SELECT ADID,PID, IntervalTimes FROM cte  AS t
WHERE  (SELECT SUM(IntervalTimes)  FROM cte WHERE row_id<=t.row_id)-IntervalTimes<DATEDIFF(second, StartTime, EndTime)
/*
ADID PID IntervalTimes
123 1 4
123 2 4
123 3 4*/

Answer 4

去掉了统计@TempaTab 数量的变量
DECLARE @TempaTab TABLE
(
  [ID] INT IDENTITY(1, 1) PRIMARY KEY,
  [ADID] varchar(20), --编码
  [StartTime] varchar(19), --开始时间
  [EndTime] varchar(19) --结束时间
)
INSERT INTO @TempaTab(ADID,StartTime,EndTime)
VALUES ('123','2003-01-01 03:44:29','2003-01-01 03:44:48')
--表2
DECLARE @TempcTab TABLE
(
  [ID] INT IDENTITY(1, 1) PRIMARY KEY,
  [ADID] varchar(20), --编码
  [PID] INT, --编号
  [IntervalTimes] INT --间隔时间(单位:秒)
)
INSERT INTO @TempcTab(ADID,PID,IntervalTimes)
VALUES('123',1,4)
INSERT INTO @TempcTab(ADID,PID,IntervalTimes)
VALUES('123',2,4)
INSERT INTO @TempcTab(ADID,PID,IntervalTimes)
VALUES('123',3,4)
DECLARE @cnt INT 
SELECT DATEDIFF(second, StartTime, EndTime) FROM  @TempaTab
--SELECT @cnt
;WITH cte AS
(
SELECT *,row_id=ROW_NUMBER()OVER( ORDER BY number,pid) 
FROM 
(
SELECT a.id,b.ADID,b.PID,IntervalTimes,a.StartTime,EndTime,cnt=COUNT(*) OVER()
FROM @TempaTab a JOIN @TempcTab b
ON a.ADID=b.ADID
) t
CROSS APPLY 
(
SELECT number FROM master..spt_values 
WHERE type='p' AND number <=DATEDIFF(second, StartTime, EndTime)/IntervalTimes/cnt
) app
)
SELECT ADID,PID, IntervalTimes FROM cte  AS t
WHERE  (SELECT SUM(IntervalTimes)  FROM cte WHERE row_id<=t.row_id)-IntervalTimes<DATEDIFF(second, StartTime, EndTime)
/*
ADID PID IntervalTimes
123 1 4
123 2 4
123 3 4
123 1 4
123 2 4*/

Answer 5

;WITH cte AS(
SELECT *
,ISNULL((SELECT SUM(IntervalTimes) FROM @TempcTab i WHERE o.ADID = i.ADID AND o.PID > i.PID),0) AS Tot1
,SUM(IntervalTimes) OVER(PARTITION BY ADID) AS Tot2
FROM @TempcTab o
)
SELECT b.ID,b.ADID,b.PID,b.IntervalTimes,DATEDIFF(S,a.[StartTime],a.[EndTime])/b.Tot2+CASE WHEN DATEDIFF(S,a.[StartTime],a.[EndTime])%b.Tot2 > b.Tot1 THEN 1 ELSE 0 END AS 次数
FROM @TempaTab a 
JOIN cte b ON a.ADID = b.ADID/*
ID          ADID                 PID         IntervalTimes 次数
----------- -------------------- ----------- ------------- -----------
1           123                  1           4             1
2           123                  2           4             1
3           123                  3           4             1(3 row(s) affected)
*/