用于从客户数据中检索丢失项目的 SQL 查询

Jules David

2024年12月30日星期一下午8:12:55

优化 SQL 以进行复杂的数据检索
SQL 是处理大量数据的强大工具，但有时查询的行为并不符合预期。例如，在处理条件查询以获取特定项目时，丢失的条目可能会带来需要仔细处理的挑战。 🧑‍💻
想象一下，运行一个查询来为客户提取数据，并且您期望某些商品代码，但它们不会出现在结果中。如果数据存在于另一个上下文中，并且您需要获取它作为后备怎么办？这需要分层查询策略，利用 SQL 的强大功能。
在“BR23456”等商品代码可能被删除或对主要客户不可用的情况下，您需要一个单独的机制来在不同参数下检索它们。本示例探讨如何解决此类问题，确保全面的数据输出。
通过逐步分解，我们将讨论如何构建一个 SQL 查询，从备用客户上下文中提取缺失的项目，同时保持效率。示例和技术将帮助您掌握动态条件的处理，为您提供对实际应用的实用见解。 🚀

命令使用示例

WITH 定义公共表表达式 (CTE)，通过允许重用中间查询结果来简化复杂查询。示例：WITH MainQuery AS (SELECT ...)

STRING_SPLIT 将分隔字符串拆分为值表，通常用于动态筛选数据。示例：从 STRING_SPLIT(@ItemCodes, ',') 中选择值

IS 将值替换为指定的替换值。对于设置默认值很有用。示例：IS(价格, 0)

TOP 1 将结果集限制为单行，通常与 ORDER BY 结合使用以获取最相关的记录。示例：从定价中选择前 1 个价格 ORDER BY start_date DESC

CASE Implements conditional logic within queries, allowing different outputs based on specific conditions. Example: CASE WHEN alvl >在查询中实现条件逻辑，允许根据特定条件输出不同的结果。示例：CASE WHEN alvl > 0 THEN 'Level 1'

NOT EXISTS 检查子查询中是否缺少行，对于处理回退逻辑很有用。示例：如果不存在（从定价中选择 1，其中商品代码 = 'BR23456'）

DECLARE 在 SQL 脚本中定义变量，用于存储临时数据或参数。示例：DECLARE @FallbackItem NVARCHAR(50) = 'BR23456'

SET NOCOUNT ON 禁用指示受查询影响的行数的消息。它提高了存储过程的性能。示例：设置 NOCOUNT ON

UNION ALL 将多个查询的结果合并到一个结果集中，包括重复的行。示例： SELECT * FROM Query1 UNION ALL SELECT * FROM Query2

ORDER BY 根据指定列对查询结果进行排序。示例：ORDER BY start_date DESC

命令	使用示例
WITH	定义公共表表达式 (CTE)，通过允许重用中间查询结果来简化复杂查询。示例：WITH MainQuery AS (SELECT ...)
STRING_SPLIT	将分隔字符串拆分为值表，通常用于动态筛选数据。示例：从 STRING_SPLIT(@ItemCodes, ',') 中选择值
IS	将值替换为指定的替换值。对于设置默认值很有用。示例：IS(价格, 0)
TOP 1	将结果集限制为单行，通常与 ORDER BY 结合使用以获取最相关的记录。示例：从定价中选择前 1 个价格 ORDER BY start_date DESC
CASE	Implements conditional logic within queries, allowing different outputs based on specific conditions. Example: CASE WHEN alvl >在查询中实现条件逻辑，允许根据特定条件输出不同的结果。示例：CASE WHEN alvl > 0 THEN 'Level 1'
NOT EXISTS	检查子查询中是否缺少行，对于处理回退逻辑很有用。示例：如果不存在（从定价中选择 1，其中商品代码 = 'BR23456'）
DECLARE	在 SQL 脚本中定义变量，用于存储临时数据或参数。示例：DECLARE @FallbackItem NVARCHAR(50) = 'BR23456'
SET NOCOUNT ON	禁用指示受查询影响的行数的消息。它提高了存储过程的性能。示例：设置 NOCOUNT ON
UNION ALL	将多个查询的结果合并到一个结果集中，包括重复的行。示例： SELECT * FROM Query1 UNION ALL SELECT * FROM Query2
ORDER BY	根据指定列对查询结果进行排序。示例：ORDER BY start_date DESC

在 SQL 查询中动态处理丢失的项目

在上面的脚本中，主要目标是解决数据检索中的一个常见问题：处理查询结果中可能缺少某些项目的情况。主脚本使用 SQL 技术的组合，例如公共表表达式 (CTE)、带有 CASE 语句的条件逻辑以及使用。通过对这些功能进行分层，查询可确保如果客户列表中缺少商品代码，它会从替代上下文中动态检索后备记录。

该解决方案的一个关键部分是使用子句定义可重用的中间查询，也称为公共表表达式 (CTE)。这使得 SQL 更易于阅读和维护，因为它将主逻辑与后备逻辑分开。例如，在 CTE 中，我们获取客户“test”的记录并检查指定列表中的商品代码。如果缺少“BR23456”等商品代码，后备查询将介入，以提供来自“lvlholder”客户的特定条件所需的数据。这保证了数据的一致性和完整性。 🛠️

另一个重要的方面是使用健康）状况。这会检查目标商品代码是否存在于主查询结果中。如果没有，脚本会从其他来源获取缺失项目的详细信息，例如备用客户或级别 (blvl = 8)。这种机制对于数据完整性至关重要的系统至关重要，例如库存管理或动态定价系统。通过使用回退逻辑，我们确保即使主要数据不完整，用户仍然收到有意义的结果。

除了回退查询之外，脚本的存储过程版本还增加了模块化和可重用性。通过参数化客户名称和商品代码等键值，可以在多个上下文中重用存储过程。这种方法还增强了性能和安全性，因为它最大限度地减少了硬编码并支持输入验证。例如，销售分析师可以使用此过程来检索具有不同后备规则的多个客户的定价数据。 🚀

最后，该解决方案采用 SQL 最佳实践来优化查询性能，例如使用和限制结果并确保获取最相关的数据。这些方法在必须高效处理大型数据集的场景中特别有用。无论您是构建仪表板还是生成报告，此类优化都可以显着改善响应时间和用户体验。

缺失数据的动态 SQL 查询处理

用于 SQL 数据库管理的后端脚本，通过后备逻辑动态处理丢失的项目。

-- Approach 1: Using a UNION query to handle missing items dynamically
WITH MainQuery AS (
    SELECT
        p.[itemcode],
        p.[uom],
        p.[trtype],
        p.[alvl],
        p.[blvl],
        CASE
            WHEN p.[alvl] > 0 THEN (
                SELECT TOP 1 x.start_date
                FROM pricing x
                WHERE x.itemcode = p.itemcode
                  AND x.blvl = p.alvl
                  AND x.customer = 'lvlholder'
                ORDER BY x.start_date DESC
            )
            WHEN p.[trtype] = '' THEN (
                SELECT TOP 1 x.start_date
                FROM pricing x
                WHERE x.itemcode = p.itemcode
                  AND x.blvl = 8
                  AND x.customer = 'lvlholder'
                ORDER BY x.start_date DESC
            )
            ELSE p.[start_date]
        END AS start_date,
        CASE
            WHEN p.[trtype] = 'Quot' THEN p.[price]
            WHEN p.[alvl] > 0 THEN (
                SELECT TOP 1 x.price
                FROM pricing x
                WHERE x.itemcode = p.itemcode
                  AND x.blvl = p.alvl
                  AND x.customer = 'lvlholder'
                ORDER BY x.start_date DESC
            )
            WHEN p.[trtype] = '' THEN (
                SELECT TOP 1 x.price
                FROM pricing x
                WHERE x.itemcode = p.itemcode
                  AND x.blvl = 8
                  AND x.customer = 'lvlholder'
                ORDER BY x.start_date DESC
            )
            ELSE 0
        END AS LevelResult,
        p.price
    FROM pricing p
    WHERE p.[Customer] = 'test'
      AND p.[itemcode] IN ('ABC1234', 'X123456', 'BR23456', 'CX23456')
)
SELECT * FROM MainQuery
UNION ALL
SELECT
    'BR23456' AS [itemcode],
    'PC' AS [uom],
    '' AS [trtype],
    0 AS [alvl],
    8 AS [blvl],
    '2024-01-01' AS start_date,
    15.56 AS LevelResult,
    0 AS price
WHERE NOT EXISTS (
    SELECT 1
    FROM MainQuery mq
    WHERE mq.[itemcode] = 'BR23456'
);

替代方法：模块化存储过程以实现可重用性

用于使用输入参数和回退逻辑处理缺失项的 SQL 存储过程。

CREATE PROCEDURE FetchItemDetails
@Customer NVARCHAR(50),
@ItemCodes NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @FallbackItem NVARCHAR(50) = 'BR23456';
    DECLARE @FallbackCustomer NVARCHAR(50) = 'lvlholder';
    DECLARE @FallbackBlvl INT = 8;
    
    -- Main Query
    SELECT
        p.[itemcode],
        p.[uom],
        p.[trtype],
        p.[alvl],
        p.[blvl],
        IS((
            SELECT TOP 1 x.start_date
            FROM pricing x
            WHERE x.itemcode = p.itemcode
              AND x.blvl = p.alvl
              AND x.customer = @FallbackCustomer
            ORDER BY x.start_date DESC
        ), p.[start_date]) AS start_date,
        IS((
            SELECT TOP 1 x.price
            FROM pricing x
            WHERE x.itemcode = p.itemcode
              AND x.blvl = p.alvl
              AND x.customer = @FallbackCustomer
            ORDER BY x.start_date DESC
        ), p.price) AS LevelResult
    FROM pricing p
    WHERE p.[Customer] = @Customer
      AND p.[itemcode] IN (SELECT value FROM STRING_SPLIT(@ItemCodes, ','));
    
    -- Fallback
    IF NOT EXISTS (SELECT 1 FROM pricing WHERE [itemcode] = @FallbackItem)
    BEGIN
        INSERT INTO pricing ([itemcode], [uom], [trtype], [blvl], [price], [start_date])
        VALUES (@FallbackItem, 'PC', '', @FallbackBlvl, 15.56, '2024-01-01');
    END
END

构建弹性 SQL 查询以实现数据完整性

SQL 查询设计中尚未讨论的一个重要方面是“外连接”的作用及其处理丢失数据的能力。与内联接不同，外联接允许您包含一张表中的所有行，即使相关表中没有相应的数据也是如此。这在处理从客户列表中检索数据（其中某些项目可能不存在）等场景时特别有用。例如，使用，您可以确保保留主表中的所有项目，并且相关表中任何缺失的数据都用空值或默认值填充。

此外，使用存储过程等工具来利用动态查询可以进一步优化 SQL 脚本。动态 SQL 允许查询根据运行时参数进行调整，从而实现灵活性。例如，您可以使用带有项目代码列表或客户名称的输入参数的存储过程，动态构建特定于情况的查询。这种方法在多租户系统中特别有用，因为不同的客户可能有不同的后备条件或要求。 🧑‍💻

最后，错误处理是构建弹性 SQL 查询时的一个关键方面。合并 try-catch 块（或其 SQL 等效项，例如使用返回码的结构化错误处理）可确保意外问题（例如丢失表或无效列引用）不会中断应用程序流程。通过结合外连接、动态 SQL 和强大的错误处理等方法，您的查询可以变得更具适应性和故障安全性，从而确保复杂场景中一致的性能和可靠性。 🚀

什么是什么时候应该使用它？
一个用于包含左表中的所有行，即使右表中没有匹配项。它对于保持报告或数据分析中的数据完整性很有用。
怎么样改善查询结果？
这函数将 null 值替换为指定值，确保数据完整性并防止计算中出现与 null 相关的错误。
有什么区别和？
仅检索表之间匹配的行，而包括不匹配的行，具体取决于类型（LEFT、RIGHT 或 FULL）。
可以使用存储过程进行动态查询吗？
是的，可以使用输入参数来设计存储过程来动态构建和执行 SQL 查询，从而提供灵活性和模块化。
错误处理如何提高查询可靠性？
SQL 中的错误处理，例如使用块，确保意外问题不会中断执行流程，从而使应用程序更加健壮。

动态 SQL 查询提供了一种强大的方法来处理可能缺少特定数据的情况。回退机制等技术可确保不会丢失关键数据点，这使得它们对于零售或物流等数据敏感行业不可或缺。通过结合高级 SQL 功能，用户可以优化性能和可靠性。

理解和利用诸如动态后备逻辑使开发人员能够创建适应各种挑战的解决方案。从定价模型到综合报告系统，这些方法可确保结果一致且准确，同时简化运营。 💡

SQL 查询结构和最佳实践源自 SQL教程。
动态查询技术和回退逻辑参考自微软 SQL Server 文档。
检索自的高级 SQL 命令的概念 GeeksforGeeks SQL 指南。
示例数据和应用场景的启发 DataCamp SQL 资源。