已知表:时间, 标识
10:00 A
10:01 A
10:02 A
...
11:01 B
11:02 B
...
12:01 A
12:02 B
12:03 B表中数据 是每个时间点的表示,数据量很大。我想遍历这个已知表记录一个新表数据,就是记录标识变化的第一个时间点。
如以上已知表 最后的结果是时间, 标识
10:00 A
11:01 B
12:01 A
12:02 B
求大虾解答。谢谢
10:00 A
10:01 A
10:02 A
...
11:01 B
11:02 B
...
12:01 A
12:02 B
12:03 B表中数据 是每个时间点的表示,数据量很大。我想遍历这个已知表记录一个新表数据,就是记录标识变化的第一个时间点。
如以上已知表 最后的结果是时间, 标识
10:00 A
11:01 B
12:01 A
12:02 B
求大虾解答。谢谢
go
insert into test values
('10:00','A'),('10:01','A'),('10:02','A'),
('11:01','B'),('11:02','B'),
('12:01','A'),('12:02','B'),('12:03','B')
go
with m as (
select crtime, name ,
row_number() over(partition by left(crtime,2), name order by crtime) rn
from test
)
select crtime, name from m where rn = 1
go
drop table test
go(8 行受影响)
crtime name
---------- ----------
10:00 A
11:01 B
12:01 A
12:02 B(4 行受影响)
go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#tb') is null
drop table #tb
Go
Create table #tb([时间] VARCHAR(5),[标识] nvarchar(21))
Insert #tb
select '10:00',N'A' union all
select '10:01',N'A' union all
select '10:02',N'A' union all
select '11:01',N'B' union all
select '11:02',N'B' union all
select '12:01',N'A' union all
select '12:02',N'B' union all
select '12:03',N'B'
Go
SELECT LEFT([时间],2)+MIN(RIGHT([时间],3)) AS [时间],[标识] from #tb GROUP BY [标识],LEFT([时间],2)/*
时间 标识
10:00 A
11:01 B
12:01 A
12:02 B
*/
select '10:00',N'A' union all
select '10:01',N'A' union all
select '10:02',N'A' union all
select '11:01',N'B' union all
select '11:02',N'B' union all
select '12:01',N'A' union all
select '12:02',N'B' union all
select '12:03',N'B'
)
SELECT t1.* FROM t AS t1
OUTER APPLY (SELECT TOP 1 ind,tm FROM t WHERE t.tm<t1.tm ORDER BY tm desc) t2
WHERE t1.ind!=ISNULL(t2.ind,'')/*
tm ind
10:00 A
11:01 B
12:01 A
12:02 B
*/
with aa(date,id)as (
select '10:00', 'A' union all
select '10:01', 'A' union all
select '10:02', 'A' union all
select '11:01', 'B' union all
select '11:02', 'B' union all
select '12:01', 'A' union all
select '12:02', 'B' union all
select '12:03', 'B'
)
select ID,MIN(date)as date from (
select DENSE_RANK()over(order by left(date,2))as NO,id,date from aa
) a
group by no,id
create table test(crtime varchar(10), name varchar(10))
create table testb(crtime varchar(10), name varchar(10))
go
insert into test values
('10:00','A'),('10:01','A'),('10:02','A'),
('11:01','B'),('11:02','B'),
('12:01','A'),('12:02','B'),('12:03','B')
go
DECLARE @namea VARCHAR(1),@nameb VARCHAR(1),@count INT, @m INT
SET @namea = ( SELECT TOP 1 name FROM test ORDER BY crtime )
SET @count = (SELECT COUNT(*) FROM test )
SET @m = 2 INSERT INTO testb SELECT TOP 1 crtime,name FROM test ORDER BY crtimeWHILE @m <= @count
BEGIN
WITH ta AS
(
SELECT ROW_NUMBER() OVER( ORDER BY crtime )numno,crtime,name FROM test
)
SELECT @nameb = name FROM ta WHERE numno = @m
IF @namea <> @nameb
BEGIN
WITH tb AS
(
SELECT ROW_NUMBER() OVER( ORDER BY crtime )numno,crtime,name FROM test
)
INSERT INTO testb SELECT crtime,name FROM tb WHERE numno = @m
END
SET @namea = @nameb
SET @m = @m + 1
END
go
select * from testb --这里就是你想要的
;WITH t(tm,ind) AS (
select '10:00',N'A' union all
select '10:01',N'A' union all
select '10:02',N'A' union all
select '11:01',N'B' union all
select '11:02',N'B' union all
select '12:01',N'A' union all
select '12:02',N'B' union all
select '12:03',N'B' union all
select '12:04',N'A'
)
,t1 as
(select *
,pid=row_number()over(partition by ind order by tm)
,rowid=row_number()over(order by tm)
from t)
select min(tm)mintm,ind from t1 group by ind,rowid-pid order by mintm
10:00 A
11:01 B
12:01 A
12:02 B
12:04 A