Get Last NOT NULL values from all the columns(从所有列中获取 Last NOT NULL 值)
问题描述
我有一种情况,我试图从所有列中获取最后一个非空值.这是一些用于演示目的的数据.
I have a situation where I am trying to get last not null value from all the columns. here is some data to for demo puposes.
测试数据
DECLARE @t TABLE (ID INT, Col1 INT, Col2 INT, Col3 INT, Dated DATETIME)
INSERT INTO @t VALUES
(1, NULL, 100 , NULL, '20131210'), --<-- Column2 from this row
(1, 20 , 200 , NULL, '20131209'), --<-- Column1 from this row
(1, 30 , NULL , 300 , '20131208'), --<-- Column3 from this row
(1, 40 , 400 , NULL, '20131207')
╔════╦══════╦══════╦══════╦═════════════════════════╗
║ ID ║ Col1 ║ Col2 ║ Col3 ║ Dated ║
╠════╬══════╬══════╬══════╬═════════════════════════╣
║ 1 ║ NULL ║ 100 ║ NULL ║ 2013-12-10 00:00:00.000 ║
║ 1 ║ 20 ║ 200 ║ NULL ║ 2013-12-09 00:00:00.000 ║
║ 1 ║ 30 ║ NULL ║ 300 ║ 2013-12-08 00:00:00.000 ║
║ 1 ║ 40 ║ 400 ║ NULL ║ 2013-12-07 00:00:00.000 ║
╚════╩══════╩══════╩══════╩═════════════════════════╝
我已经建立了一个返回所需结果的查询.但想知道是否有更高效的方法来做到这一点.由于这种方式我每次都在查询整个表,我希望找到一种更有效的方法来获得这些结果.
I have built a query which returns the required results. But was wondering if there is a more performance efficient way of doing this. Since this way I am quering the whole table each time I hope to find a more efficient way of getting these results.
我的查询
SELECT ColumnName, Col1 AS Value, Dated
FROM
(
SELECT TOP 1 'Column1' AS ColumnName
, Col1
, Dated
FROM @t
WHERE Col1 IS NOT NULL
ORDER BY Dated DESC
)Q1
UNION ALL
SELECT * FROM
(
SELECT TOP 1 'Column2' AS ColumnName
, Col2
, Dated
FROM @t
WHERE Col2 IS NOT NULL
ORDER BY Dated DESC
)Q2
UNION ALL
SELECT * FROM
(
SELECT TOP 1 'Column3' AS ColumnName
, Col3
, Dated
FROM @t
WHERE Col3 IS NOT NULL
ORDER BY Dated DESC
)Q3
结果集
╔════════════╦═══════╦═════════════════════════╗
║ ColumnName ║ Value ║ Dated ║
╠════════════╬═══════╬═════════════════════════╣
║ Column1 ║ 20 ║ 2013-12-09 00:00:00.000 ║
║ Column2 ║ 100 ║ 2013-12-10 00:00:00.000 ║
║ Column3 ║ 300 ║ 2013-12-08 00:00:00.000 ║
╚════════════╩═══════╩═════════════════════════╝
它返回正确的结果.但我确信可以通过 更简单/高效的查询 来完成.任何帮助或指针都非常受欢迎
It returns the correct results. but I am sure it can be done with a bit more simpler/efficient query . Any help or pointer is much appericiated
推荐答案
非常简单UNPIVOT
操作和适当的 ROW_NUMBER
选择最近的值.UNPIVOT
自动消除 NULL
值:
Pretty much a straightforward UNPIVOT
operation and an appropriate ROW_NUMBER
to select the most recent value. The UNPIVOT
automatically eliminates NULL
values:
;With Numbered as (
select *,ROW_NUMBER() OVER (PARTITION BY Col ORDER BY Dated desc) rn
from @t
unpivot (Value for Col in (Col1,Col2,Col3)) p
)
select * from Numbered where rn = 1
结果:
ID Dated Value Col rn
----------- ----------------------- ----------- -------- --------------------
1 2013-12-09 00:00:00.000 20 Col1 1
1 2013-12-10 00:00:00.000 100 Col2 1
1 2013-12-08 00:00:00.000 300 Col3 1
这篇关于从所有列中获取 Last NOT NULL 值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:从所有列中获取 Last NOT NULL 值
基础教程推荐
- 使用pyodbc“不安全"的Python多处理和数据库访问? 2022-01-01
- 如何在 SQL Server 的嵌套过程中处理事务? 2021-01-01
- SQL Server:只有 GROUP BY 中的最后一个条目 2021-01-01
- SQL Server 2016更改对象所有者 2022-01-01
- 将数据从 MS SQL 迁移到 PostgreSQL? 2022-01-01
- 无法在 ubuntu 中启动 mysql 服务器 2021-01-01
- Sql Server 字符串到日期的转换 2021-01-01
- 在 VB.NET 中更新 SQL Server DateTime 列 2021-01-01
- SQL Server 中单行 MERGE/upsert 的语法 2021-01-01
- ERROR 2006 (HY000): MySQL 服务器已经消失 2021-01-01