时间戳

首页 > 解决方案 > 将记录展平为值不为空的单行

sql-server - 将记录展平为值不为空的单行

问题描述

我知道有一堆“扁平化”问题，但它们似乎不符合这个要求。

给定一个包含 4 列的数据表，所有这些列都可以有空值，我需要能够指定一个特定的深度值并返回一条记录，该记录将在该深度和更低（接近 1）处搜索以填补空白.

COL1      COL2          COL3         COL4   DEPTH
--------- ------------- ------------ ------ -----------
NULL      NULL          Manager      NULL   9
NULL      NULL          NULL         NULL   8
Jack      NULL          NULL         36     7
NULL      NULL          Employed     28     6
James     NULL          NULL         15     5
NULL      Ericson       NULL         NULL   4
NULL      NULL          NULL         23     3
Jack      NULL          NULL         NULL   2
John      Smith         Unemployed   45     1

例如，请求深度 5 应该返回：

COL1      COL2        COL3           COL4   DEPTH
--------- ----------- -------------- ------ -----
James     Ericson     Unemployed     15     5

示例设置：

DECLARE @Table TABLE
(
    [COL1] varchar(30) NULL,
    [COL2] varchar(30) NULL,
    [COL3] varchar(30) NULL,
    [COL4] varchar(30) NULL,
    [DEPTH] int NOT NULL
);

INSERT INTO @Table
SELECT Null     , Null      , 'Manager'     , Null  , 9 UNION ALL
SELECT Null     , Null      , Null          , Null  , 8 UNION ALL
SELECT 'Jack'   , Null      , Null          , '36'  , 7 UNION ALL
SELECT Null     , Null      , 'Employed'    , '28'  , 6 UNION ALL
SELECT 'James'  , Null      , Null          , '15'  , 5 UNION ALL
SELECT Null     , 'Ericson' , Null          , Null  , 4 UNION ALL
SELECT Null     , Null      , Null          , '23'  , 3 UNION ALL
SELECT 'Jack'   , Null      , Null          , Null  , 2 UNION ALL
SELECT 'John'   , 'Smith'   , 'Unemployed'  , '45'  , 1;

SELECT * FROM @Table ORDER BY DEPTH DESC;

当前工作代码：

DECLARE @Depth int = 5;
SELECT 
    [COL1] = ( SELECT TOP(1) [COL1] FROM @Table WHERE [DEPTH] <= @Depth AND [COL1] IS NOT Null ORDER BY DEPTH DESC ),
    [COL2] = ( SELECT TOP(1) [COL2] FROM @Table WHERE [DEPTH] <= @Depth AND [COL2] IS NOT Null ORDER BY DEPTH DESC ),
    [COL3] = ( SELECT TOP(1) [COL3] FROM @Table WHERE [DEPTH] <= @Depth AND [COL3] IS NOT Null ORDER BY DEPTH DESC ),
    [COL4] = ( SELECT TOP(1) [COL4] FROM @Table WHERE [DEPTH] <= @Depth AND [COL4] IS NOT Null ORDER BY DEPTH DESC );

有没有更好的方法来检索数据？我已经尝试了一些东西，但没有任何其他方法有效，更不用说更好了。

标签： sql-servertsqlaggregatesql-server-2016flatten

解决方案

您可能会使用 XML 技巧：

DECLARE @tbl TABLE(COL1 VARCHAR(100),COL2 VARCHAR(100),COL3 VARCHAR(100),COL4 INT,DEPTH INT);
INSERT INTO @tbl VALUES
 (NULL,NULL   ,'Manager',NULL,9)
,(NULL,NULL   ,NULL   ,NULL,8)
,('Jack',NULL   ,NULL   ,36  ,7)
,(NULL,NULL   ,'Employed',28  ,6)
,('James',NULL   ,NULL   ,15  ,5)
,(NULL,'Ericson',NULL   ,NULL,4)
,(NULL,NULL   ,NULL   ,23  ,3)
,('Jack',NULL   ,NULL   ,NULL,2)
,('John','Smith'  ,'Unemployed',45  ,1);

DECLARE @dpth INT=5;

WITH DataAsXml(TheXml) AS
(
    SELECT t.*
    FROM @tbl t
    WHERE t.DEPTH<=@dpth
    ORDER BY t.DEPTH DESC  
    FOR XML PATH('row'),TYPE
)
SELECT TheXml.value('(/row/COL1/text())[1]','varchar(100)') AS COL1 
      ,TheXml.value('(/row/COL2/text())[1]','varchar(100)') AS COL2
      ,TheXml.value('(/row/COL3/text())[1]','varchar(100)') AS COL3
      ,TheXml.value('(/row/COL4/text())[1]','int') AS COL4
      ,TheXml.value('(/row/DEPTH/text())[1]','int') AS DEPTH
FROM DataAsXml;

中间 XML 如下所示：

<row>
  <COL1>James</COL1>
  <COL4>15</COL4>
  <DEPTH>5</DEPTH>
</row>
<row>
  <COL2>Ericson</COL2>
  <DEPTH>4</DEPTH>
</row>
<row>
  <COL4>23</COL4>
  <DEPTH>3</DEPTH>
</row>
<row>
  <COL1>Jack</COL1>
  <DEPTH>2</DEPTH>
</row>
<row>
  <COL1>John</COL1>
  <COL2>Smith</COL2>
  <COL3>Unemployed</COL3>
  <COL4>45</COL4>
  <DEPTH>1</DEPTH>
</row>

如您所见，XML 默认会省略 NULL 值。该代码将按降序对列表进行排序。使用 XQuery 获取第一个值将返回最顶层的非空值。

推荐阅读