CASE WHEN将结果集分配给查询

Question

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

根据所选公司的不同，应该显示一组筛选结果，这是我的代码。

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

为什么不允许我这样做？

Answer 1

以下是使用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是唯一的，否则可能会对查询计划重用造成灾难。

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

你能创建一个新的交叉引用表，让数据库将公司映射到inventolocationid吗？-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;

Answer 2

作为单个查询编写，这是等效的逻辑。根据数据的大小，if..else分支可能会更好地工作。

SELECT INVENTLOCATINID
FROM INVENTDIM INV
WHERE 
INV.DATAAREAID = @COMPANY AND INVENTLOCATIONID IS NOT NULL WMSLOCATIONID <> '' AND
(
@COMPANY = 'Company name1' AND INV.INVENTLOCATIONID = 'x5' OR
@COMPANY = 'Company_name2' AND INVENTLOCATIONID IN
('X0', 'X1','X2','X3','X5','X6','X8','P6','P8') OR
@COMPANY NOT IN ('Company name1', 'Company name2')
);

Answer 3

尝试下面的查询以获得所需的输出

DECLARE @COMPANY VARCHAR(10) = 'Company_name1'
declare @tblIL as table
(
INVENTLOCATIONID int
)
IF(@COMPANY = 'Company_name1')
BEGIN
insert into @tblIL(INVENTLOCATIONID) values ('x5')
END
ELSE IF(@COMPANY = 'Company_name2')
BEGIN
insert into @tblIL(INVENTLOCATIONID) values ('X0', 'X1','X2','X3','X5','X6','X8','P6','P8')
END
ELSE
BEGIN
insert into @tblIL(INVENTLOCATIONID) values SELECT DISTINCT INVENTLOCATIONID FROM INVENTDIM
END
SELECT INVENTLOCATIONID FROM INVENTDIM INV 
WHERE INV.DATAAREAID = @COMPANY AND INVENTLOCATIONID IS NOT NULL AND WMSLOCATIONID IS NOT NULL AND WMSLOCATIONID <> '' 
AND INV.INVENTLOCATIONID in (select INVENTLOCATIONID from @tblIL)