gpt4 book ai didi

sql - 在重复项中选择不同的连续行

转载 作者:行者123 更新时间:2023-12-02 17:32:44 26 4
gpt4 key购买 nike

我有一个结构如下的表:

create table roster
(
date date not null,
first nvarchar(20) not null,
second nvarchar(20) not null,
third nvarchar(20) not null,
fourth nvarchar(20) not null,
)
go

并且插入了以下数据:

insert into roster values ('2015-06-10 12:45:34', 'e', 'm', 'a', 'r')
insert into roster values ('2015-06-11 12:45:34', 'e', 'v', 'a', 'r')
insert into roster values ('2015-06-12 12:45:34', 'e', 'm', 'a', 'd')
insert into roster values ('2015-06-13 12:45:34', 'e', 'm', 'a', 'd') *
insert into roster values ('2015-06-14 12:45:34', 'e', 'm', 'a', 'r')
insert into roster values ('2015-06-15 12:45:34', 'e', 'm', 'a', 'r') *
insert into roster values ('2015-06-16 12:45:34', 'z', 'm', 't', 'r')

注意:* 标记重复。

如何只选择“第一”、“第二”、“第三”和“第四”的唯一连续组合?例如,对于上面插入的数据,期望的输出是:

Date                 First Second Third Fourth
2015-06-10 12:45:34, e m a r
2015-06-11 12:45:34, e v a r
2015-06-12 12:45:34, e m a d
2015-06-14 12:45:34, e m a r
2015-06-16 12:45:34, z m t r

我正在寻找一种解决方案,当条目不再连续时(或序列被破坏时)保留条目,但删除连续条目的重复项。

我在这里看到过类似的问题,但我已经能够制定一个使用分组依据的解决方案。

任何帮助将不胜感激

最佳答案

如果您只需要一个 exists(),为什么还需要 group by

;with data as
(
select ROW_NUMBER() OVER (ORDER BY date) AS number, * from roster
)
select * from data where
not exists -- Just compare with the previous column, if match say bye
(
select * from data prev where 1 = 1
and prev.first = data.first
and prev.second = data.second
and prev.third = data.third
and prev.fourth = data.fourth
and prev.number + 1 = data.number
)

SQL Fiddle

编辑

;with data as
(
select
ROW_NUMBER() OVER (ORDER BY date) AS number,
ROW_NUMBER() OVER (PARTITION BY first, second, third, fourth ORDER BY date) AS part,
*
from roster
)
select MIN(date) as startdate, MAX(date) as enddate, COUNT(*) count, first, second, third, fourth
from data group by first, second, third, fourth, number - part
order by number - part

SQL Fiddle

关于sql - 在重复项中选择不同的连续行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/30773101/

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