SQL Server逻辑分组的最新时间 [英] SQL Server logical grouping most recent time
问题描述
我正在寻找解决此问题的最佳方法，到目前为止还没有任何运气. 我正在使用Microsoft SQL Server 2008.
I am looking for the best approach to this issue and have not had any luck so far. I am using Microsoft SQL Server 2008.
这是用户的示例数据:
=====================
======================
名称状态日期
=====================
======================
BOB有效2011年3月7日
BOB Active 2011-03-07
BOB活动时间2011-03-11
BOB Active 2011-03-11
BOB残障人士2011-03-15
BOB Disabled 2011-03-15
BOB残障人士2011-03-21
BOB Disabled 2011-03-21
BOB活动时间2011-03-23
BOB Active 2011-03-23
BOB活动时间2011-03-28
BOB Active 2011-03-28
=====================
======================
我希望将其分组，以便显示用户实际更改状态的时间，而不是下次检查结果是否保持不变时的下一次显示，以便查询返回的内容如下:
I want to have this grouped so that it will show when the user actually changed the status and not the next time they were checked if the result stayed the same so what the query returns would be as follows:
=====================
======================
BOB有效2011年3月7日
BOB Active 2011-03-07
BOB残障人士2011-03-15
BOB Disabled 2011-03-15
BOB活动时间2011-03-23
BOB Active 2011-03-23
=====================
======================
这是我无权访问的数据.
This is data that I do not have any access how it is created.
关于按我需要的方式获取此数据的最佳方法的任何想法吗?请记住，这是因为数千记录，并且在可能的情况下应考虑性能.我想不出一种方法来进行分组，这样就不会将"Activate"分组在一起并使用MIN聚合函数.
Any ideas on the best approach for getting this data the way I need? Keep in mind this is for Thousands of records and performance should be taken into consideration if possible. I can't think of a way to do the grouping so that it will not group the "Activate"s together and use the MIN aggregate function.
是否有一种方法可以循环遍历并使数据看起来像这样，以便在最后一列进行分组和分组?这甚至是最好的方法吗?
Is there a way to maybe loop through and make the data look like this for grouping and group on the last column? Would this even be the best approach?
=====================
======================
名称状态日期组
=====================
======================
BOB活动时间2011-03-07 1
BOB Active 2011-03-07 1
BOB活动时间2011-03-11 1
BOB Active 2011-03-11 1
BOB残障人士2011-03-15 2
BOB Disabled 2011-03-15 2
BOB残障人士2011-03-21 2
BOB Disabled 2011-03-21 2
BOB活动时间2011-03-23 3
BOB Active 2011-03-23 3
BOB活动时间2011-03-28 3
BOB Active 2011-03-28 3
=====================
======================
如果有人可以给我一个关键词来进行搜索，以使我走上正确的道路，那我将不胜感激.
All of my searches have not worked out, if someone could give me a key word to search for to get me on the right path that would be very much appreciated.
推荐答案
select tb.name,tb.status,tb.date
from the_table tb
join (select name,min(date) as date from the_table group by name) t_aggr on tb.name=t_aggr.name and tb.date=t_aggr.date
union
select tb2.name,tb2.status,tb2.date
from the_table tb1
join the_table tb2 on tb1.name=tb2.name and tb1.status<>tb2.status and tb2.date>tb1.date
left join the_table tb3 on tb1.name=tb2.name and tb1.date<tb3.date and tb3.date<tb2.date
where tb3.date is null
联合中的第一个选择是获取第一个记录.第二个应该得到更改.无论如何都要测试它，因为我没有SQL Server可以尝试.
The first select in union is to get the first record. The second should get the changes. Anyway test it as I have no sql server to try it on.
这篇关于SQL Server逻辑分组的最新时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
