在SQL中按日期排序在每个组中查找多次出现值的首次出现 [英] Finding first occurence of multiples value in every group sort by date in SQL

问题描述

我有一个表，其中的每个操作都在事件组之前附加另一个值.

I have a table with every operations that appends before an event group by another value.

只有3种操作:R，E，P

There is only 3 operations: R, E, P

+ ----------+----------+-----------+------------------------+
| Rollcycle | Blocking | Operation | Order                  |
+ ----------+----------+-----------+------------------------+
| 1         | 3        | R         | 4                      |
| 1         | 3        | P         | 3                      |
| 1         | 3        | E         | 2                      |
| 1         | 3        | R         | 1                      |
| 1         | 2        | P         | 3                      |
| 1         | 2        | E         | 2                      |
| 1         | 2        | R         | 1                      |
| 1         | 1        | R         | 1                      |
| 2         | 1        | E         | 2                      |
| 2         | 1        | R         | 1                      |
+ ----------+----------+-----------+------------------------+

我想知道在Rollcycle的每个阻止组之前发生了哪些操作. 我需要在Access SQL中执行此操作.

I want to know which operations occurs before every blocking group by Rollcycle. I need to do this in access SQL.

输出

+ ----------+----------+---+---+---+
| Rollcycle | Blocking | R | E | P |
+ ----------+----------+---+---+---+
| 1         | 1        | 1 | 0 | 0 |
| 1         | 2        | 0 | 1 | 1 |
| 1         | 3        | 1 | 0 | 0 |
| 2         | 1        | 1 | 1 | 0 |
+ ----------+----------+---+---+---+

我找不到类似的东西.可能太具体了.

I could not find anything similar. It's maybe too specific.

请帮助:)

返回原始表

推荐答案

如果我正确地跟随了您，则可以过滤order大于或等于blocking的记录，然后进行条件聚合:

If I followed you correctly, you can filter on records where order is greater or equal than blocking, and then do conditional aggregation:

select
    rollcycle,
    blocking,
    max(iif(operation = 'R', 1, 0)) R,
    max(iif(operation = 'E', 1, 0)) E,
    max(iif(operation = 'P', 1, 0)) P
from mytable 
where order >= blocking
group by rollcycle, blocking

这篇关于在SQL中按日期排序在每个组中查找多次出现值的首次出现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！