本文介绍了查询出历史数据表?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
场景:显示状态注册信息的历史数据表.数据示例如下:
| ID | State | Status | AsOfDate |
|---|---|---|---|
| 2021 | AZ | A | 2001-05-21 |
| 2021 | AZ | T | 2004-12-31 |
| 2021 | AZ | A | 2010-01-01 |
| 2021 | CA | A | 2001-05-21 |
| 2021 | WA | A | 2001-05 -21 |
| 2023 | AZ | A | 2005-01-04 |
| 2023 | MD | A | 2003-07-01 |
| 2023 | WA | A | 2002-05-21 |
| 2023 | WA | T | 2007-05-21 |
ID =字段代表标识号State =代表销售代表获得工作许可的状态Status =表示活动或已终止的标志AsOfDate =状态更改的日期我需要弄清楚该查询，该查询将返回一组对于给定ID当前处于活动状态的不同状态.也就是说，2021年应返回{AZ，CA，WA}(AZ已终止，随后重新激活)，而2023年应返回{AZ，MD}(WA仍终止.)
我认为这是一个简单的查询，但我不太清楚.
The scenario: A table of historical data showing state registration information. An example of the data looks like this:
| ID | State | Status | AsOfDate |
|---|---|---|---|
| 2021 | AZ | A | 2001-05-21 |
| 2021 | AZ | T | 2004-12-31 |
| 2021 | AZ | A | 2010-01-01 |
| 2021 | CA | A | 2001-05-21 |
| 2021 | WA | A | 2001-05-21 |
| 2023 | AZ | A | 2005-01-04 |
| 2023 | MD | A | 2003-07-01 |
| 2023 | WA | A | 2002-05-21 |
| 2023 | WA | T | 2007-05-21 |
ID = field rep identification numberState = state where a rep is licensed to workStatus = flag indicating Active or TerminatedAsOfDate = the date when the status changedI need to figure out the query that will return the set of distinct states that are currently active for a given ID. That is to say, 2021 should return {AZ, CA, WA} (AZ was terminated and later reactivated) and 2023 should return {AZ, MD} (WA is still terminated.)
I would think this is a simple one, but I can''t quite figure it out.
推荐答案
SELECT T.* FROM TestT T
JOIN (SELECT ID, State, MAX(AsOfDate) AsOfDate FROM TestT
GROUP BY ID, State) A ON T.Id = A.Id AND T.AsOfDate = A.AsOfDate
WHERE T.id = 2023
AND Status = 'A'
在此脚本中，TestT是包含您的数据的实际表.根据需要更改T.Id.
希望对您有帮助.
In this script TestT is the actual table which contain your data. Change the T.Id as per your need.
Hope this will help you.
这篇关于查询出历史数据表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！