时间戳

首页 > 解决方案 > CASE WHEN 将结果集分配给查询

sql - CASE WHEN 将结果集分配给查询

问题描述

我一直在尝试根据输入为查询分配不同的结果集

根据选择的公司，应该显示一组过滤结果，这是我的代码。

DECLARE @COMPANY VARCHAR(10) = 'Company_name1'

SELECT 
    (CASE 
        WHEN @COMPANY = 'Company_name1'
           THEN (SELECT INVENTLOCATIONID FROM INVENTDIM INV 
                 WHERE (INV.DATAAREAID = @COMPANY) 
                   AND (((INVENTLOCATIONID IS NOT NULL) 
                   AND ((WMSLOCATIONID IS NOT NULL) 
                   AND (WMSLOCATIONID <> '')))
                   AND (INV.INVENTLOCATIONID = 'x5'))
        WHEN @COMPANY = 'Company_name2'
           THEN (SELECT INVENTLOCATIONID FROM INVENTDIM INV 
                 WHERE (INV.DATAAREAID = @COMPANY) 
                   AND (((INVENTLOCATIONID IS NOT NULL)
                   AND ((WMSLOCATIONID IS NOT NULL)
                   AND (WMSLOCATIONID <> '')))
                   AND (INVENTLOCATIONID IN ('X0', 'X1', 'X2', 'X3', 'X5', 'X6', 'X8', 'P6', 'P8')))
           ELSE (SELECT INVENTLOCATIONID FROM INVENTDIM INV 
                 WHERE (DATAAREAID = @COMPANY) 
                   AND ((INVENTLOCATIONID IS NOT NULL) 
                   AND ((WMSLOCATIONID IS NOT NULL) 
                   AND (WMSLOCATIONID <> '')))
      END) AS WHAREWOUSE
FROM 
    INVENTDIM INV 
ORDER BY 
    INV.INVENTLOCATIONID

为什么我不允许这样做？

标签： sqlsql-serverdatabase

解决方案

CASE这是您可以使用表达式执行此操作的一种方法。使用常量来拒绝公司名称与特定案例匹配且位置不在您的列表中的条目。如果公司不是特例之一，那就接受吧。

DECLARE @COMPANY VARCHAR(10) = 'Company_name1';

SELECT WHAREHOUSE = INVENTLOCATIONID
FROM dbo.INVENTDIM
  WHERE DATAAREAID = @COMPANY
  AND INVENTLOCATIONID IS NOT NULL
  AND WMSLOCATIONID IS NOT NULL
  AND WMSLOCATIONID <> ''
AND 1 = 
(
    CASE 
      WHEN DATAAREAID = 'Company_name1' 
           AND INVENTLOCATIONID <> 'x5' 
           THEN 0
      WHEN DATAAREAID = 'Company_name2' 
           AND INVENTLOCATIONID NOT IN ('X0','X1','X2','X3','X5','X6','X8','P6','P8') 
           THEN 0
      ELSE 1
    END
)
ORDER BY INVENTLOCATIONID;

但是，除非DATAAREAID是唯一的，否则查询计划重用可能是一场灾难。

这绝对是一种更好的方法：

您能否创建一个新的交叉引用表以允许数据库将公司映射到 inventlocationid？– Zynon Putney II

假设您有一个映射表，例如：

CREATE TABLE dbo.LocationMap
(
  DATAAREAID varchar(10),
  INVENTLOCATIONID char(2),
  PRIMARY KEY(DATAAREAID, INVENTLOCATIONID
);

INSERT dbo.LocationMap(DATAAREAID,INVENTLOCATIONID)
VALUES
('Company_Name1', 'x5'),
('Company_Name2', 'X0'), ('Company_Name2', 'X1') -- , ...

然后您的查询变为：

DECLARE @COMPANY VARCHAR(10) = 'Company_name1';

SELECT WHAREHOUSE = INV.INVENTLOCATIONID
FROM dbo.INVENTDIM AS INV
LEFT OUTER JOIN dbo.LocationMap AS lm
  ON INV.DATAAREAID = lm.DATAAREAID
WHERE INV.DATAAREAID = @COMPANY
      AND INV.INVENTLOCATIONID IS NOT NULL
      AND INV.WMSLOCATIONID IS NOT NULL
      AND INV.WMSLOCATIONID <> ''
      AND (lm.INVENTLOCATIONID = INV.INVENTLOCATIONID
           OR lm.DATAAREAID IS NULL)
ORDER BY INV.INVENTLOCATIONID;

推荐阅读