时间戳

首页 > 解决方案 > SQL Query 重复 `AND` 查询，如何避免？

sql - SQL Query 重复 `AND` 查询，如何避免？

问题描述

我在这里有这个查询：

DECLARE @date Date;

SET @date = '2020-11-24';

SELECT p.description AS ProductDescription, 
    SUM(CASE 
            WHEN p.code LIKE '%400' THEN requiredQty * 10
            WHEN p.code LIKE '%400OL' THEN requiredQty * 10
            WHEN p.code LIKE '%396' THEN requiredQty * 6
            WHEN p.code LIKE '%396OL' THEN requiredQty * 6
            WHEN p.code LIKE '%101' THEN requiredQty * 1
            WHEN p.code LIKE '%101OL' THEN requiredQty * 1
            WHEN p.code LIKE '%393OL' THEN requiredQty * 3
        ELSE 0 END) AS RequiredQty,
    '1kg' AS PackSize
FROM SalesOrderLine sol JOIN 
    SalesOrder so
    ON sol.salesOrderID = so.id JOIN
    Product p 
    ON sol.productID = p.id
WHERE 
    p.code like '%396' AND
    so.dueDate = @date
    OR
    p.code like '%396OL' AND
    so.dueDate = @date
    OR
    p.code like '%400' AND
    so.dueDate = @date
    OR
    p.code like '%400OL' AND
    so.dueDate = @date
    OR
    p.code like '%101' AND
    so.dueDate = @date
    OR
    p.code like '%101OL' AND
    so.dueDate = @date
    OR
    p.code like '%393OL' AND
    so.dueDate = @date
GROUP BY p.description;

问题

我AND so.dueDate = @date多次使用它来获得准确的数据。没有它，我将获得多年前的数据。

也许我的这个查询很啰嗦？有没有办法避免一遍又一遍地重复相同的查询？

标签： sqlsql-server

解决方案

您可以通过多种方式简化查询：

SELECT p.description AS ProductDescription, 
       SUM(requiredQty * v.factor) as RequiredQty,
       '1kg' AS PackSize
FROM SalesOrderLine sol JOIN 
     SalesOrder so
     ON sol.salesOrderID = so.id JOIN
     Product p 
     ON sol.productID = p.id JOIN
     (VALUES ('%400', 10), ('%400L', 10),
             ('%396', 6), ('%396L', 6),
             ('%101', 1), ('%101L', 1),
             ('%393OL', 3)
     ) v(pattern, factor)
     ON p.code LIKE v.pattern
WHERE so.dueDate = @date
GROUP BY p.description;

JOIN负责过滤。

注意：由于LIKE你的多次使用以及最多匹配一行的事实，我认为aboutJOIN不会特别昂贵。CASE但是您可以使用以下表达式替换逻辑APPLY：

SELECT p.description AS ProductDescription, 
       SUM(requiredQty * v.factor) as RequiredQty,
       '1kg' AS PackSize
FROM SalesOrderLine sol JOIN 
     SalesOrder so
     ON sol.salesOrderID = so.id JOIN
     Product p 
     ON sol.productID = p.id CROSS APPLY
     (VALUES (CASE WHEN p.code LIKE '%400' THEN 10
                   WHEN p.code LIKE '%400OL' THEN 10
                   WHEN p.code LIKE '%396' THEN 6
                   WHEN p.code LIKE '%396OL' THEN 6
                   WHEN p.code LIKE '%101' THEN 1
                   WHEN p.code LIKE '%101OL' THEN 1
                   WHEN p.code LIKE '%393OL' THEN 3
              END)
     ) v(factor)
WHERE so.dueDate = @date AND
      f.factor IS NOT NULL
GROUP BY p.description;

推荐阅读