时间戳

首页 > 解决方案 > 为什么此查询在 STRING_SPLIT 上跳过

tsql - 为什么此查询在 STRING_SPLIT 上跳过

问题描述

我正在尝试将列上的一个数据拆分GLACCTNBR为多个列，但某些记录没有正确拆分并最终得到一个Null值。

WITH C AS(
      SELECT distinct  
      m.ACCTID
      ,m.ITEMDESC
      ,m.GLACCTNBR
      ,m.TOTAL
      ,m.DEPFILENBR
      ,m.DEPFILESEQ
      ,m.ITEMACCTID
      ,m.AMOUNT
      ,value 
      ,ROW_NUMBER() OVER(PARTITION BY m.GLACCTNBR ORDER BY (SELECT 1)) as rn
     FROM TG_ITEM_DATA m 
     inner join TG_TRAN_DATA t on m.DEPFILENBR = t.DEPFILENBR 
     AND m.DEPFILESEQ = t.DEPFILESEQ 
     AND m.EVENTNBR = t.EVENTNBR 
     AND m.TRANNBR = t.TRANNBR 
     AND t.VOIDDT IS NULL 
     AND NOT(t.ITEMIND='T') 
     AND m.GLACCTNBR IS NOT NULL 
     CROSS APPLY STRING_SPLIT(m.GLACCTNBR, '|') AS BK
     WHERE ( 

    (m.DEPFILENBR=2019261 AND m.DEPFILESEQ=1)) 
     AND m.ACCTID IS NOT NULL
)
SELECT distinct depfilenbr, depfileseq,acctid,GLACCTNBR,ITEMACCTID,AMOUNT,
      [1] AS PLUDEP
      ,[2] AS FUND
      ,[3] AS REVCD
      ,[4] AS SUBREVCD
      ,[5] AS BALSJT

FROM C
PIVOT(
    max(VALUE)
    FOR RN IN([1],[2],[3],[4],[5])  
) as PVT

GROUP BY DEPFILENBR,depfileseq,acctid,GLACCTNBR,ITEMACCTID,AMOUNT,[1],[2],[3],[4],[5]

depfilenbr  depfileseq  acctid  GLACCTNBR           ITEMACCTID  AMOUNT  PLUDEP  FUND    REVCD   SUBREVCD    BALSJT
2019261              1  5053    08|48R|3800|00|0000 50-0028      38.41      08   48R     3800         00      0000
2019261              1  5053    08|48R|3896|00|0000 50-001           0    NULL  NULL     NULL       NULL      NULL

标签： tsqlsplitpivotpivot-tablecommon-table-expression

解决方案

值得一提的是，在任何情况下都应避免使用分隔数据。它违反了 1.NF，并且在查询中确实令人头疼。如果有机会在输入端改变这个问题，你应该先这样做。

由于您正在使用STRING_SPLIT()，您必须在 v2016+ 上。

比这个奇怪的要好得多STRING_SPLIT()，它不返回片段的位置并且不保证以预期的顺序返回，是 JSON 的一个技巧：

DECLARE  @SomeDelimitedString VARCHAR(100)='part1|part2|part3';

DECLARE @JsonArray NVARCHAR(MAX)=CONCAT('["',REPLACE(@SomeDelimitedString,'|','","'),'"]');

SELECT @SomeDelimitedString          AS TheOriginal
      ,@JsonArray                    AS TransformedToJSON
      ,JSON_VALUE(@JsonArray,'$[0]') AS TheFirstFragment
      ,JSON_VALUE(@JsonArray,'$[1]') AS TheSecondFragment
      ,JSON_VALUE(@JsonArray,'$[2]') AS TheThirdFragment

结果

part1|part2|part3   ["part1","part2","part3"]   part1   part2   part3

简而言之：

我们使用一些简单的字符串操作将分隔字符串转换为 JSON 数组。现在我们可以使用 JsonPath 通过它的位置来抓取片段。

您可以阅读此答案以了解更多详细信息（v2016+ 的 UPDATE 部分和类型安全结果的 UPDATE 2 部分）。

我无法对此进行测试，但您可以尝试以下方法：

  SELECT   
   m.ACCTID
  ,m.ITEMDESC
  ,m.GLACCTNBR
  ,m.TOTAL
  ,m.DEPFILENBR
  ,m.DEPFILESEQ
  ,m.ITEMACCTID
  ,m.AMOUNT
  ,JSON_VALUE(JsonArray,'$[0]') AS PLUDEP 
  ,JSON_VALUE(JsonArray,'$[1]') AS FUND 
  ,JSON_VALUE(JsonArray,'$[2]') AS REVCD 
  ,JSON_VALUE(JsonArray,'$[3]') AS SUBREVCD 
  ,JSON_VALUE(JsonArray,'$[4]') AS BALSJT 
 FROM TG_ITEM_DATA m 
 inner join TG_TRAN_DATA t on m.DEPFILENBR = t.DEPFILENBR 
 AND m.DEPFILESEQ = t.DEPFILESEQ 
 AND m.EVENTNBR = t.EVENTNBR 
 AND m.TRANNBR = t.TRANNBR 
 AND t.VOIDDT IS NULL 
 AND NOT(t.ITEMIND='T') 
 AND m.GLACCTNBR IS NOT NULL 
 CROSS APPLY (SELECT CONCAT('["',REPLACE(m.GLACCTNBR,'|','","'),'"]')) BK(JsonArray)
 WHERE m.DEPFILENBR=2019261 
   AND m.DEPFILESEQ=1 
   AND m.ACCTID IS NOT NULL
--GROUP BY m.DEPFILENBR,m.depfileseq,m.acctid,m.GLACCTNBR,m.ITEMACCTID,m.AMOUNT,PLUDEP,FUND,REVCD,SUBREVCD,BALSJT

推荐阅读