sql - 通过在列中应用条件来获取数据
问题描述
我正在编写一个查询来获取两组数据。这是按 desc 排序后数据的样子
Select RecordId,Tab, Status, CreatedDate
from Table1
Order By Tab, CreatedDate Desc
记录 ID | 标签 | 地位 | 创建日期 |
---|---|---|---|
1 | 100 | 1 | 2021-10-13 08:15:02.154 |
1 | 100 | 2 | 2021-10-13 08:04:05.456 |
2 | 200 | 2 | 2021-10-13 08:13:00.198 |
2 | 200 | 1 | 2021-10-13 08:07:09.052 |
2 | 200 | 3 | 2021-10-13 08:04:03.306 |
3 | 300 | 1 | 2021-10-13 08:12:02.051 |
3 | 300 | 2 | 2021-10-13 08:08:05.158 |
3 | 300 | 3 | 2021-10-13 08:02:01.756 |
4 | 400 | 3 | 2021-10-13 08:14:01.356 |
4 | 400 | 1 | 2021-10-13 08:09:05.753 |
4 | 400 | 2 | 2021-10-13 08:05:06.152 |
第一组:只有那些当前状态为 1 的 RecordId(在我们的例子中是 RecordId 1 和 3)
第二组:只有那些 RecordId,它之前的状态为 1 但当前已更改(在我们的示例中为 RecordId 2 和 4)
请帮我写一个关于这个的查询。
解决方案
也许这是您正在寻找的:
Select RecordId as FirstSet
from Table1 t1
where t1.Status = 1 and not exists (
select * from Table1 t2 where t2.RecordId = t1.RecordId and t2.CreatedDate > t1.CreatedDate
)
Select RecordId as SecondSet
from Table1 t1
where t1.Status = 1 and exists (
select * from Table1 t2 where t2.RecordId = t1.RecordId and t2.CreatedDate > t1.CreatedDate
)