SQL Server - 解决这与基于集的解决方案，而不是行迭代 [英] SQL Server - Solve this with set-based solution instead of row iteration

问题描述

我试图将我的一些业务逻辑从我的程序和存储过程。我真的是一个VB.NET程序员，而不是一个SQL专家，但我正在学习更多的SQL，发现在很多情况下，让SQL做我的处理和返回少量的数据，而不是给我更快

I'm trying to move some of my business logic out of my programs and into stored procedures. I'm really a VB.NET programmer, and not a SQL expert, but I'm learning more SQL and finding that in a lot of cases, it's faster to let SQL do my processing and return small amounts of data rather than giving me a ton of stuff and having my programs chomp through it.

所以，我目前的问题是：

So, my current problem is this:

我正在创建一个从数据库中的几个不同来源发生的时间轴。我提取的相关信息是：

I'm creating a timeline of something that has occurred from several different sources in a database. The pertinent information I am pulling is:

1. 用户名


2. 与操作相关联的时间

我想使用这个时间表来确定谁在给定时间负责某个给定的事情。因此，如果1个用户在新用户记录某事之前连续记录了400个动作，我真的不在乎;我只想看看用户1开始记录的时间，以及用户2接管记录时。

I want to use this timeline to figure out, ultimately, who was responsible for a given thing at a given time. Thus, if 1 user logs 400 actions in a row before a new user logs something, I really don't care; I just want to see when user 1 started logging and when user 2 took over logging.

更多图形示例：

User1 | 12:00

User1 | 12:01

User1 | 12:02

User1 | 12:03

User1 | 12:04

User1 | 12:05

User1 | 12:06

User2 | 12:07

User2 | 12:08

User2 | 12:09

User2 | 12:10

User2 | 12:11

User1 | 12:12

User1 | 12:13

User1 | 12:00
User1 | 12:01
User1 | 12:02
User1 | 12:03
User1 | 12:04
User1 | 12:05
User1 | 12:06
User2 | 12:07
User2 | 12:08
User2 | 12:09
User2 | 12:10
User2 | 12:11
User1 | 12:12
User1 | 12:13

我想要什么：

User1 | 12:00

User2 | 12:07

User1 | 12:12

User1 | 12:00
User2 | 12:07
User1 | 12:12

现在，在代码中，我将结果集放到一个DataTable中，并重复表中的每一行。然后，我将检查当前行的[用户名]值与上一行的[用户名]值，并且仅当[用户名]不同时才添加当前行的值。在真正的SQL专家使用游标似乎有一个普遍的厌恶，但我不知道我还以这种方式，所以任何人都可以帮助我在这里？

Now, in code, I'd get that result set into a DataTable and iterate each row in the table. Then, I'd check the current row's [User Name] value against the previous row's [User Name] value and only add the current row's values if the [User Name] was different. There seems to be a general aversion among true SQL experts to using a cursor, but I'm not sure I yet think in that manner, so can anyone help me out here?

到目前为止，我已经成功地将原始的未过滤的数据导入我的查询中的表变量。因此，我只需要知道如何折叠数据，只返回一小部分。

So far, I have successfully gotten the raw, unfiltered data into a table variable in my query. So, I just need to know how to "collapse" the data and only return a small subset.

谢谢！

推荐答案

EDIT 需要1个级别的间接过滤才能进行排序：

EDIT Needs 1 more level of indirection for filtering by rank to work:

select 
  User,Time
from
(
  select *
  from
  (
      Select 
        User,Time, rank() over (partition by u.User order by u.Time) as User_Rank
      from 
        your_table u
  ) UserRanks
) x
where User_Rank = 1
order by Time

与araqnid和Royi的答案类似，但使用WHERE NOT EXISTS而不是JOIN。

Similar to araqnid and Royi's answers, but using WHERE NOT EXISTS rather than JOIN.

with CTE  as (
    select user, time, row_number() over (order by time) rn from MyTable
)  
select    CTE.user, CTE.time
from CTE CTE1
where not exists (select user, time from CTE CTE2 where CTE1.rn = CTE2.rn - 1 and CTE1.user = CTE2.user)

这篇关于SQL Server - 解决这与基于集的解决方案，而不是行迭代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！