主页 / dba / 问题 / 343714
Accepted
ToC
Asked: 2024-11-20 03:53:40 +0800 CST

Sql Server：查询以解析和验证代码

  • 772

我们有一个#ValidCode表，其中列出了有效代码，例如：“A”、“B”、“C”等。另一个名为#SourceData的表包含输入数据——它是有效和无效标记的组合（有时是重复的）。

前任：

  • ‘A;B;C’ (有效)
  • ‘A;A;A;A;A;B’ (有效)
  • 'ad;df;A;B'（无效）

尝试找到一种最佳查询方法来处理这些字符串以在#SourceData中找到有效行。请参阅以下示例：

DROP TABLE IF EXISTS #ValidCode
GO
CREATE TABLE #ValidCode
(
      ID        INT             IDENTITY(1,1)
    , Code      CHAR(1)
)
INSERT INTO #ValidCode (Code) VALUES ('A'), ('B'), ('C')
GO
DROP TABLE IF EXISTS #SourceData 
GO
CREATE TABLE #SourceData 
(
      ID        INT             IDENTITY(1,1)
    , Codes     VARCHAR(500)
    , Is_Valid  BIT
    , Is_Split  BIT
)

INSERT INTO #SourceData (Codes) 
VALUES    ('A;B;C')
        , ('B;A')
        , ('B;B;B;C;C;A;A;B')
        , ('B;Z;1')
        , ('B;ss;asd')


SELECT * FROM #ValidCode
SELECT * FROM #SourceData

查询将处理#SourceData表中的数据并更新Is_Valid标志，以便它们可以在后续过程中被使用。

规则：

  • 每个标记都必须对整个列行有效（第 1 行至第 3 行）
  • 即使一个标记无效，整个行值也无效（第 4 行和第 5 行）

因此，这是首选的输出：

ID 代码 是否有效
1 A;B;C 1
2 B;A 1
3 B;B;B;C;C;A;A;B 1
4 B;Z;1 0
5 B;ss;asd 0

当前方法：循环遍历#SourceData中的每一行，并根据分隔符“；”将它们拆分，然后将它们与#ValidCode表进行比较。如果所有标记单独有效，则将#SourceData中的行标记为有效（Is_Valid标志）。否则标记为无效。WHILE循环方法有效，但速度很慢。

#SourceData最多可以有 300 万行。每行都有多个重复的有效值('A;A;A;A')和无效值组合('A;as;sdf;B')

有没有更好的方法？

谢谢！

query-performance

2 个回答

  1. ——首先想到的是：

    SELECT 
    sd.ID
    , sd.Codes
    , CASE 
        WHEN NOT EXISTS (
            SELECT x.[value] 
            FROM string_split(sd.Codes, ';') as x
            LEFT OUTER JOIN #ValidCode as vc
                ON x.[value] = vc.Code
            WHERE vc.Code IS NULL
        )
        THEN 1
        ELSE 0
      END as is_valid
FROM #SourceData as sd

    为分成几行的 SourceData 代码创建一个子表可能更为理想。

    • 1
  2. Best Answer

    您可以通过一种关系方式来执行此操作，即拆分#SourceData第一个（幸运的是，尽管使用的是过时的 SQL Server 版本，您仍然可以访问该STRING_SPLIT()函数），然后获取与您的不匹配的行#ValidCodes，最后使用这些行来确定Is_Valid原始#SourceData表中的内容。

    以下是如何做到这一点的示例：

    ;WITH _BadData AS
(
    SELECT DISTINCT SD.ID
    FROM #SourceData AS SD
    CROSS APPLY STRING_SPLIT(SD.Codes, ';') AS SS
    WHERE NOT EXISTS
    (
        SELECT 1 AS RowExists
        FROM #ValidCode AS VC
        WHERE SS.[value] = VC.Code
    )
)

SELECT
    SD.ID,
    SD.Codes,
    ISNULL(SD.Is_Valid, IIF(BD.ID IS NULL, 1, 0)) AS Is_Valid,
    SD.Is_Split
FROM #SourceData AS SD
LEFT JOIN _BadData AS BD
    ON SD.ID = BD.ID;

    这里有一个 dbfiddle.uk repo，演示了该代码。

    请注意，根据的大小#SourceData和生成的执行计划，您可能希望STRING_SPLIT() 先将函数的结果、整个 CTE 本身或两者具体化到临时表中，然后再在上述查询的后半部分中使用它来获取最终结果LEFT JOIN。但我认为这应该比逐行循环要好得多。

    • 1

相关问题