sql-server - 如何只获得不同的行

Question

我有一张如下图所示的表格

id     locale   roaming   rent
301 NULL    18.00     NULL
300 NULL    NULL      5.00
299 11.00   NULL      NULL
298 NULL    NULL      4.00
297 NULL    20.00     NULL
296 NULL    NULL      6.00
295 9.00    NULL      NULL
294 NULL    20.00     NULL
293 10.00   NULL      NULL

我想在一行中的每一列（没有 id 列）中只获取一个值。我该怎么做？但我只想在一个选择查询中执行此操作。

标签： sql-server

Answer 1

一个可能的解决方案：

SELECT (
        SELECT TOP 1 [local]
        FROM tbl
        WHERE [local] IS NOT NULL
        ORDER BY [id] DESC
        ) AS [local]
    ,(
        SELECT TOP 1 [roaming]
        FROM tbl
        WHERE [roaming] IS NOT NULL
        ORDER BY [id] DESC
        ) AS [roaming]
    ,(
        SELECT TOP 1 [rent]
        FROM tbl
        WHERE [rent] IS NOT NULL
        ORDER BY [id] DESC
        ) AS [rent]