如何对这些数据进行分组，以便从每个组中提取特定的行 [英] How to group this data so I can pull out specific rows from each group

问题描述

我有下面显示的数据集。因此，我想选择每个组中的第一行，其中 PersonID 的状态已经更改为与上一个状态不同的状态。

I have the dataset shown below. From this, I want to select the first row from each group where the PersonIDs status has changed to a different status than the previous one.

例如，从这个数据集中，我想要1,4,7和11行。

对此有帮助吗？

如果我做了一个 GROUPBY ，它将所有新建和所有挂起组合在一起。

For example, from this dataset, I would want the rows 1, 4, 7 and 11.
Any help on this?
If I do a GROUPBY, it just lumps together all New and all Pending in 2 groups.

PersonID    Status  WhenChanged
101         New     27/01/2017 15:27
101         New     27/01/2017 16:40
101         New     27/01/2017 16:40
101         Pending 27/01/2017 16:40
101         Pending 27/01/2017 16:40
101         Pending 27/01/2017 16:40
101         New     31/01/2017 09:14
101         New     31/01/2017 10:02
101         New     31/01/2017 10:03
101         New     31/01/2017 10:05
101         Pending 03/02/2017 14:29
101         Pending 03/02/2017 14:29

推荐答案

下面是使用CTE的方法。由于您使用的日期是以varchar格式显示或因此显示，所以排序不起作用。

Here's a go at it...using a CTE. The ordering is off because your date you used was in a varchar format or displayed as such. You can convert it just the same and this will work fine.

declare @table table (PersonID int, Status varchar(16), WhenChanged varchar(64))
insert into @table values
(101,'New','27/01/2017 15:27'),
(101,'New','27/01/2017 16:40'),
(101,'New','27/01/2017 16:40'),
(101,'Pending','27/01/2017 16:40'),
(101,'Pending','27/01/2017 16:40'),
(101,'Pending','27/01/2017 16:40'),
(101,'New','31/01/2017 09:14'),
(101,'New','31/01/2017 10:02'),
(101,'New','31/01/2017 10:03'),
(101,'New','31/01/2017 10:05'),
(101,'Pending','03/02/2017 14:29'),
(101,'Pending','03/02/2017 14:29')

;with cte as(
    select
        PersonID,
        Status,
        WhenChanged,
        case when lag(Status) over (partition by PersonID order by convert(datetime,WhenChanged,103)) <> Status then 1 else 0 end as d
    from
        @table)



select top 1 *
from @table
union 
select  PersonID,
        Status,
        WhenChanged
from cte
where d=1
order by WhenChanged

SQL Server 2008和以前版本

declare @table table (PersonID int, Status varchar(16), WhenChanged varchar(64))
insert into @table values
(101,'New','27/01/2017 15:27'),
(101,'New','27/01/2017 16:40'),
(101,'New','27/01/2017 16:40'),
(101,'Pending','27/01/2017 16:40'),
(101,'Pending','27/01/2017 16:40'),
(101,'Pending','27/01/2017 16:40'),
(101,'New','31/01/2017 09:14'),
(101,'New','31/01/2017 10:02'),
(101,'New','31/01/2017 10:03'),
(101,'New','31/01/2017 10:05'),
(101,'Pending','03/02/2017 14:29'),
(101,'Pending','03/02/2017 14:29')


;with cte as(
    select 
        PersonID,
        Status,
        convert(datetime,WhenChanged,103) as WhenChanged,
        row_number() over (order by personID, convert(datetime,WhenChanged,103)) as RN
    from
        @table),

cteResults as(
    select
        PersonID,
        Status,
        WhenChanged
    from
        cte 
    where RN = 1
    UNION
    select
        c.PersonID,
        c.Status,
        c.WhenChanged 
    from
        cte c
    inner join
        cte c2 on c2.rn = (c.rn -1) and c2.status <> c.status)

select * from cteResults order by WhenChanged

这篇关于如何对这些数据进行分组，以便从每个组中提取特定的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！