問題描述

我有一個包含很多行的表格，其中有幾個與全局相關的標識符，但在我當前的任務中，只有 3 列是重要的:

I have a table that contains A LOT of rows, with several identifiers that are relevant in the big picture, but in my current task, only 3 columns are important:

SiteIdentifier | SysTm | Signalet

SiteIdentifier 是一個數字，SysTm 是 DateTime，Signalet 是一個文本字段.防爆數據:

SiteIdentifier is a number, SysTm is DateTime and Signalet is a text field. Ex data:

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

這是我想要實現的目標:我想創建一個可以顯示的查詢:

Here's what I want to achieve: I want to create a query that can display:

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

在示例數據行的情況下，結果將是:獲取每個 SiteIdentifier 的每個實例很重要

In the case of the lines of example data, a result would be: It is important that I get EVERY instance of EVERY SiteIdentifier

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

(每個 SiteIdentifier 可以表示 NUMEROUS 次)

這些行都包含在一張表中，這讓我很困惑...

These rows are all contained in one table, which is what is tripping me up...

我之前問過這個問題，并得到了這個查詢:

I asked this question earlier, and was given this query:

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 可以表示多次.

This query gave me the most recent "row" of EACH SiteIdentifier, but not all results. I am trying to get the result to be, as I wrote above, a SiteIdentifier can be represented multiple times.

推薦答案

如果您在 SiteIdentifier 中為每個 Signalet Left"添加行號和Joined"，然后在匹配的行上加入你會得到想要的結果.

If you add a row number across the SiteIdentifier for each of Signalet "Left" and "Joined", and then join on the matching row you get the desired results.

注意:添加了笨重的第二個左連接來處理第一行不是左"記錄的情況.

Note: Added a clunky second left join to handle the case when the first row isn't a 'left' record.

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;

返回:

請注意，如果您像我對您的問題所做的那樣添加 DDL+DML，您會更容易為人們提供幫助.

Note if you add DDL+DML as I have done to your questions you make it much easier for people to assist.

這篇關于如何在不使用 MAX 聚合的情況下將多行顯示為一行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！