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

How to display multiple rows, as one row, without using a MAX aggregation(如何在不使用 MAX 聚合的情况下将多行显示为一行)
本文介绍了如何在不使用 MAX 聚合的情况下将多行显示为一行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

我有一个包含很多行的表格,其中有几个与全局相关的标识符,但在我当前的任务中,只有 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;

返回:

<头>
站点标识符SysTm(左)SysTm(已加入)差异
5874512021-03-01 00:00:002021-03-04 07:12:1779
5874512021-03-04 12:12:122021-03-08 01:04:0785
2145372021-03-05 02:13:032021-03-05 07:13:005

请注意,如果您像我对您的问题所做的那样添加 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 聚合的情况下将多行显示为一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Convert varchar to float IF ISNUMERIC(将 varchar 转换为 float IF ISNUMERIC)
multi part identifier could not be bound sql(无法绑定多部分标识符 sql)
Any benefit to explicitly dropping local temporary tables at the end of a stored procedure?(在存储过程结束时显式删除本地临时表有什么好处?)
Check if a column contains text using SQL(使用 SQL 检查列是否包含文本)
Select value if condition in SQL Server(SQL Server 中的条件选择值)
Control flow in T-SQL SP using IF..ELSE IF - are there other ways?(使用 IF..ELSE IF 在 T-SQL SP 中控制流 - 还有其他方法吗?)