用于对一系列事件进行分组的Funky SQL Server查询 [英] Funky SQL Server query that groups bunches of events
问题描述
嘿!
我想要一个对类似事件进行分组的SQL服务器查询,但分组值可能会多次出现,例如:
我有一个数据库存储一个人所在的位置。该数据库包含:
走廊
走廊
厨房
厨房
厨房
起居室
厨房
起居室
起居室
走廊
我想要查询以产生以下结果:
2走廊
3厨房
1客厅
1厨房
2客厅
1走廊
因此,在同一地点分组,但前提是他们必须紧随其后。谁能帮我?哦,顺便说一句......最后我需要一个产生结果的Linq查询(在EF 6上),所以写linq immidiately没问题...
以下是一个示例linq查询:带有Partition By子句模拟的Row_Number() [ ^ ]
关于SQL查询的想法是一样的;)
注意:当人们看到时,你需要按日期(日)设置分区彼此;)
声明 @ tb table (str1 varchar ( 50 ))
insert into @ tb (str1) values
(' 走廊'),
(' 走廊'),
(' Kitchen'),
(' Kitchen'),
(' Kitchen'),
(' Livingroom'),
(' Kitchen'),
(' Livingroom'),
(' Livingroom'),
(' Hallway' )
; cte0(str1,rno)
as
(
选择 str1,rno = row_number() over (< span class =code-keyword> order by ( SELECT 1 ))来自 @ tb
),
cte1(str1,rno,pro) as
(
select str1,rno,pno = rno- row_number() over ( partition by str1 order by cte0.rno,str1) from cte0
)
select count(*) as cnt,t.str1 来自 cte0 t inner join cte1 t1 t.rno = t1.rno group by t.str1,t1.pro order by min(t .rno)
Hey!
I want to have a sql server query that groups similar events, but the 'grouping value' may occur multiple times, for example:
I have a database which stores where "a person" was seen. The database contains :
Hallway
Hallway
Kitchen
Kitchen
Kitchen
Livingroom
Kitchen
Livingroom
Livingroom
Hallway
I want te query to produce te following result :
2 Hallway
3 Kitchen
1 Livingroom
1 Kitchen
2 Livingroom
1 Hallway
Thus, group by the same location but only if they immidiately follow eachother up. Can anyone help me? Oh, and by the way... in the end I need a Linq query (on EF 6) that produces the result, so writing linq immidiately is no problem...
Here is an example linq query: Row_Number() with Partition By clause simulation[^]
The idea on SQL query is the same ;)
Note: you need to set partition by date (day) when person seeen each other ;)
declare @tb table(str1 varchar(50)) insert into @tb (str1) values ('Hallway'), ('Hallway'), ('Kitchen'), ('Kitchen'), ('Kitchen'), ('Livingroom'), ('Kitchen'), ('Livingroom'), ('Livingroom'), ('Hallway') ;with cte0(str1,rno) as ( select str1, rno=row_number() over(order by (SELECT 1)) from @tb ), cte1 (str1,rno,pro) as ( select str1,rno,pno=rno- row_number() over(partition by str1 order by cte0.rno,str1) from cte0 ) select count(*) as cnt, t.str1 from cte0 t inner join cte1 t1 on t.rno=t1.rno group by t.str1,t1.pro order by min(t.rno)
这篇关于用于对一系列事件进行分组的Funky SQL Server查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!