gpt4 book ai didi

SQL Server如何按顺序选择第一行

转载 作者:行者123 更新时间:2023-12-04 23:46:26 27 4
gpt4 key购买 nike

我有一个包含以下示例数据的表格:

Tag          Loc        Time1
A 10 6/2/15 8:00 AM
A 10 6/2/15 7:50 AM
A 10 6/2/15 7:30 AM
A 20 6/2/15 7:20 AM
A 20 6/2/15 7:15 AM
B 10 6/2/15 7:12 AM
B 10 6/2/15 7:11 AM
A 10 6/2/15 7:10 AM
A 10 6/2/15 7:00 AM

我需要 SQL 来选择序列中的第一行(最早的),直到位置发生变化,然后再次选择最早的行,直到位置发生变化。换句话说,我需要上面的以下输出:

Tag         Loc         Time1
A 10 6/2/15 7:30 AM
A 20 6/2/15 7:15 AM
A 10 6/2/15 7:00 AM
B 10 6/2/15 7:11 AM

我从 Giorgos 尝试过这个 - 但是选择中的一些行是重复的:

declare @temptbl table (rowid int primary key identity, tag nvarchar(1), loc int, time1 datetime)
declare @tag as nvarchar(1), @loc as int, @time1 as datetime

insert into @temptbl (tag, loc, time1) values (1,20,'6/5/2015 7:15 AM')
insert into @temptbl (tag, loc, time1) values (1,20,'6/5/2015 7:20 AM')
insert into @temptbl (tag, loc, time1) values (1,20,'6/5/2015 7:25 AM')
insert into @temptbl (tag, loc, time1) values (4,20,'6/5/2015 7:20 AM')
insert into @temptbl (tag, loc, time1) values (4,20,'6/5/2015 7:25 AM')
insert into @temptbl (tag, loc, time1) values (4,20,'6/5/2015 7:30 AM')
insert into @temptbl (tag, loc, time1) values (4,20,'6/5/2015 7:35 AM')
insert into @temptbl (tag, loc, time1) values (4,20,'6/5/2015 7:40 AM')

select * from @temptbl

SELECT Tag, Loc, MIN(Time1) as time2
FROM (
SELECT Tag, Loc, Time1,
ROW_NUMBER() OVER (ORDER BY Time1) -
ROW_NUMBER() OVER (PARTITION BY Tag, Loc
ORDER BY Time1) AS grp
FROM @temptbl ) t

GROUP BY Tag, Loc, grp

这是结果(每个标签应该只有一行)

Tag  Loc time2
1 20 2015-06-05 07:15:00.000
1 20 2015-06-05 07:25:00.000
4 20 2015-06-05 07:20:00.000
4 20 2015-06-05 07:30:00.000

最佳答案

假设您使用的是 MS SQL Server 2012 或更新版本,lag窗口函数将允许您将一行与前一行进行比较:

SELECT tag, loc, time1
FROM (SELECT tag, loc, time1,
LAG (loc) OVER (PARTITION BY tag ORDER BY time1) AS lagloc
FROM my_table) t
WHERE loc != lagloc OR lagloc IS NULL

关于SQL Server如何按顺序选择第一行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/30615798/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com