如何在不使用MAX聚合的情况下将多行显示为一行

Question

我有一个包含很多行的表，其中有几个与全局相关的标识符，但在我当前的任务中，只有3列是重要的：

SiteIdentifier | SysTm | Signalet

SiteIdentifier是一个数字，SysTm是DateTime，Signalet是一个文本字段。Ex数据：

587451 | 2021-03-01 00:00:00 | Left
587451 | 2021-03-04 07:12:17 | Joined
214537 | 2021-03-05 02:13:03 | Left
587451 | 2021-03-04 12:12:12 | Left
214537 | 2021-03-05 07:13:00 | Joined
587451 | 2021-03-08 01:04:07 | Joined

以下是我想要实现的目标：我想创建一个可以显示的查询：

SiteIdentifier | SysTm of last Signalet Left | SysTm of last Signalet Joined | DATEDIFF(hour, ...  between last Left and Joined

在示例数据行的情况下，结果是：重要的是，我要获得每个站点标识符的每个实例

587451 | 2021-03-01 00:00:00 | 2021-03-04 07:12:17 | 79
214537 | 2021-03-05 02:13:03 | 2021-03-05 07:13:00 | 5
587451 | 2021-03-04 12:12:12 | 2021-03-08 01:04:07 | 84

(每个站点标识符可以表示无数次(

这些行都包含在一个表中，这就是让我绊倒的地方。。。

我早些时候问了这个问题，得到了这样的询问：

SELECT SiteIdentifier,
MAX(CASE WHEN Signalet = 'Left' THEN SysTM END) as left_tm,
MAX(CASE WHEN Signalet = 'Joined' THEN SysTM END) as Joined_tm,
DATAEDIFF(hour,
MAX(CASE WHEN Signalet = 'Left' THEN SysTM END),
MAX(CASE WHEN Signalet = 'Joined' THEN SysTM END)
) as time_diff
FROM Table
WHERE Signalet IN ( 'Left', 'Joined')
GROUP BY SiteIdentifier
ORDER BY SiteIdentifier

这个问题给了我最近的"；行"；EACH SiteIdentifier的，但不是所有结果。正如我在上面所写的，我正在努力使结果成为一个SiteIdentifier可以被表示多次

Answer 1

如果在SiteIdentifier上为每个Signalet添加一个行号"左"；以及"；Joined"；，然后加入匹配的行，得到所需的结果。

注意：当第一行不是"left"记录时，添加了一个笨拙的第二个left联接来处理这种情况。

declare @Test table (SiteIdentifier int, SysTm datetime2(0), Signalet varchar(21));
insert into @Test (SiteIdentifier, SysTm, Signalet)
values
(587451, '2021-03-01 00:00:00', 'Left'),
(587451, '2021-03-04 07:12:17', 'Joined'),
(214537, '2021-03-05 02:13:03', 'Left'),
(587451, '2021-03-04 12:12:12', 'Left'),
(214537, '2021-03-05 07:13:00', 'Joined'),
(587451, '2021-03-08 01:04:07', 'Joined');
with cte as (
select *
, row_number() over (partition by SiteIdentifier, Signalet order by SysTm) rn
from @Test
)
select C1.SiteIdentifier, C1.SysTm, coalesce(C2.SysTm, C3.SysTm), datediff(hour, C1.SysTm, coalesce(C2.SysTm, C3.SysTm))
from cte C1
left join cte C2 on C2.SiteIdentifier = C1.SiteIdentifier and C2.Signalet = 'Joined' and C2.rn = C1.rn and C2.SysTm > C1.SysTm
left join cte C3 on C3.SiteIdentifier = C1.SiteIdentifier and C3.Signalet = 'Joined' and C3.rn = C1.rn + 1 and C3.SysTm > C1.SysTm and C2.rn is null
where C1.Signalet = 'Left'
order by C1.SysTm asc;

退货：

站点标识符	SysTm(左(	系统Tm(已连接(	差异
587451	2021-03-01 00:00:00	2021:03-04 07:12:17	79
587451	2021-03-04 12:12:12	2021-03-08 01:04:07	85
214537	2021-03-05 02:13:03	2021:03-05 07:13:00	5