3

## SQL 依時間排序 連續資料的分組

DTime　　　　　　　　No
2020/05/07 08:44:08　A
2020/05/07 08:44:53　A
2020/05/07 08:45:38　B
2020/05/07 08:46:23　B
2020/05/07 08:47:08　B
2020/05/07 08:47:53　A
2020/05/07 08:48:38　A
2020/05/07 08:49:23　C

1　　　A　　2020/05/07 08:44:08 2020/05/07 08:44:53
2　　　B　　2020/05/07 08:45:38 2020/05/07 08:47:08
3　　　A　　2020/05/07 08:47:53 2020/05/07 08:48:38
4　　　C　　2020/05/07 08:49:23 2020/05/07 08:49:23

hichu314 iT邦新手 5 級 ‧ 2021-06-07 16:59:40 檢舉

### 4 個回答

5

iT邦研究生 1 級 ‧ 2021-06-07 20:00:31

``````;WITH CTE AS (
SELECT NCode,MIN(DTime) 'minTime',MAX(DTime) 'MaxTime'
FROM (
SELECT *, ROW_NUMBER() OVER(ORDER BY DTime) -
ROW_NUMBER() OVER(PARTITION BY NCode ORDER BY DTime) rn
FROM da
) t1
GROUP BY rn,NCode
)
SELECT ROW_NUMBER() OVER(ORDER BY minTime) no,*
FROM CTE
``````

Note:

sqlfiddle

0
paicheng0111
iT邦高手 1 級 ‧ 2021-06-07 17:37:18
``````select a.*, b.minimun, b.maximum
from
(select distinct [Ncode], [team] from da) a
left join
(select max([dtime]) as maximum, min([dtime]) as minimun, [ncode], [team] FROM da GROUP BY [ncode], [team]) b
on a.[ncode]=b.[ncode] and a.[team]=b.[team]
``````
hichu314 iT邦新手 5 級 ‧ 2021-06-07 17:54:57 檢舉

1

iT邦大師 1 級 ‧ 2021-06-07 17:57:40
``````with t0(id, dtime, no) as (
select row_number() over(order by dtime, no)
, *
from it210607b
), t1(id, dtime, no, cflag) as (
select *
, coalesce(no <> lag(no) over(order by id), True)
from t0
), t2(id, dtime, no, cflag, rn) as (
select *
, row_number() over(order by id)
from t1
where t1.cflag = True
), t3 as (
select t1.*
, t2.rn
from t1
left join t2
on (t1.id = t2.id)
), t4(dtime, no, grp) as (
select dtime, no
, max(rn) over(partition by no order by id, rn)
from t3
)
select grp, no
, min(dtime)
, max(dtime)
from t4
group by grp, no
order by grp, no;

grp | no |         min         |         max
-----+----+---------------------+---------------------
1 | A  | 2020-05-07 08:44:08 | 2020-05-07 08:44:08
2 | B  | 2020-05-07 08:45:38 | 2020-05-07 08:47:08
3 | A  | 2020-05-07 08:47:53 | 2020-05-07 08:48:38
4 | C  | 2020-05-07 08:49:23 | 2020-05-07 08:49:23
5 | A  | 2020-05-07 09:44:53 | 2020-05-07 09:44:53
(5 rows)
``````
hichu314 iT邦新手 5 級 ‧ 2021-06-07 18:04:12 檢舉

1　　　A　　2020/05/07 08:44:08 2020/05/07 08:44:08
2　　　B　　2020/05/07 08:45:38 2020/05/07 08:47:08
3　　　A　　2020/05/07 08:47:53 2020/05/07 08:48:38
4　　　C　　2020/05/07 08:49:23 2020/05/07 08:49:23
5　　　A　　2020/05/07 09:44:53 2020/05/07 09:44:53

1

iT邦高手 1 級 ‧ 2021-06-07 18:29:53

``````declare @da table
([DTime] datetime, [NCode] varchar(10))
;

INSERT INTO @da
([DTime], [NCode])
VALUES
('2020/05/07 08:44:08', 'A'),
('2020/05/07 09:44:53', 'A'),
('2020/05/07 08:45:38', 'B'),
('2020/05/07 08:46:23', 'B'),
('2020/05/07 08:47:08', 'B'),
('2020/05/07 08:47:53', 'A'),
('2020/05/07 08:48:38', 'A'),
('2020/05/07 08:49:23', 'C')

declare @dk table
([DTime] datetime, [NCode] varchar(10),Sort int)
;

select no=Identity(int,1,1),* Into #temptable
from @da
declare @Sort int=0,@TmpStr1 nvarchar(50) = '',@TmpStr2 nvarchar(50) = '',@i int = 0,@TotalNum int = 0
select @TotalNum=Count(0) from #temptable
while(@i<@TotalNum)
begin
set @i = @i + 1
select @TmpStr1=NCode
from #temptable
where no=@i
if(@TmpStr1<>@TmpStr2)
begin
set @TmpStr2 = @TmpStr1
set @Sort = @Sort + 1
end
insert into @dk
select [DTime]
,[NCode]
,@Sort
from #temptable
where no=@i
end
Drop Table #temptable

select Sort
,[NCode]
,Min(DTime) Min_DTime
,Max(DTime) Max_DTime
from @dk
group by Sort
,[NCode]
order by Sort
``````