gpt4 book ai didi

sql - MSSQL 2008 中的行合并,

转载 作者:行者123 更新时间:2023-12-03 02:34:23 29 4
gpt4 key购买 nike

我正在尝试确定 MSSQL 2008 中的最佳方法。

这是我的示例数据

TransDate  Id     Active
-------------------------
1/18 1pm 5 1
1/18 2pm 5 0
1/18 3pm 5 Null
1/18 4pm 5 1
1/18 5pm 5 0
1/18 6pm 5 Null

如果按 Id 分组并按 TransDate 排序,我需要事件列的最后一个非空值以及 TransDate 的最大值

SELECT MAX(TransDate) AS TransDate, 
Id,
--LASTNonNull(Active) AS Active

结果如下:

TransDate  Id  Active
---------------------
1/18 6pm 5 0

这就像合并,但在行上,而不是两个值/列。

还有许多其他列也会应用这种类似的方法,所以我真的不想为每个列进行单独的连接。

有什么想法吗?

最佳答案

我可能会使用相关子查询。

SELECT MAX(TransDate)             AS TransDate,
Id,
(SELECT TOP (1) Active
FROM T t2
WHERE t2.Id = t1.Id
AND Active IS NOT NULL
ORDER BY TransDate DESC) AS Active
FROM T t1
GROUP BY Id

没有的方法

SELECT
Id,
MAX(TransDate) AS TransDate,
CAST(RIGHT(MAX(CONVERT(CHAR(23),TransDate,121) + CAST(Active AS CHAR(1))),1) AS BIT) AS Active,
/*You can probably figure out a more efficient thing to
compare than the above depending on your data. e.g.*/
CAST(MAX(DATEDIFF(SECOND,'19500101',TransDate) * CAST(10 AS BIGINT) + Active)%10 AS BIT) AS Active2
FROM T
GROUP BY Id

或者按照评论交叉应用对您来说效果更好?

WITH T (TransDate, Id, Active, SomeOtherColumn) AS
(
select GETDATE(), 5, 1, 'A' UNION ALL
select 1+GETDATE(), 5, 0, 'B' UNION ALL
select 2+GETDATE(), 5, null, 'C' UNION ALL
select 3+GETDATE(), 5, 1, 'D' UNION ALL
select 4+GETDATE(), 5, 0, 'E' UNION ALL
select 5+GETDATE(), 5, null,'F'

),
T1 AS
(
SELECT MAX(TransDate) AS TransDate,
Id
FROM T
GROUP BY Id
)
SELECT T1.TransDate,
Id,
CA.Active AS Active,
CA.SomeOtherColumn AS SomeOtherColumn
FROM T1
CROSS APPLY (SELECT TOP (1) Active, SomeOtherColumn
FROM T t2
WHERE t2.Id = T1.Id
AND Active IS NOT NULL
ORDER BY TransDate DESC) CA

关于sql - MSSQL 2008 中的行合并,,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/4729984/

29 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com