SQL Server - Solve this with set-based solution in

问题:

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. A user name
2. A time associated with an action

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.

More graphical example:

User | Time

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

What I'd like:

User Time

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

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.

Thanks!

回答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

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)

回答2:

This is one of those exceptions where a cursor is likely your best bet. Just try to limit the subset of data that you are going to iterate as much as you can.

回答3:

Finally :

;with CTE  as (
    select user, time, row_number() over (order by time) rn from MyTable
)  
select    CTE.user, CTE.time
from CTE left join CTE other on other.rn = CTE .rn - 1
where other.user is null or CTE .user <> other.user

回答4:

A row-based iteration is probably your best solution in SQL Server. Other database flavours allow you to example values from the previous/next row (lag and lead window functions), but SQL Server doesn't support those.

You could bodge something together like this:

with x as (
    select user, time, row_number() over (order by time) rn from source
)
select x.user, x.time
from x left join x prev on prev.rn = x.rn - 1
where prev.user is null or x.user <> prev.user

However, I suspect this is inconvenient and performs abominably.