sql - 如何选择列组合与值列表匹配的行？

Question

假设我有一个包含如下值的表:

CREATE TABLE foo 
(
  ID int NOT NULL,
  Box1 varchar(50),
  Box2 varchar(50),
  Box3 varchar(50),
  CONSTRAINT PK_foo PRIMARY KEY (ID)
);

INSERT INTO foo (ID, Box1, Box2, Box3) VALUES 
  (0, 'FOOBIE BLETCH', NULL, NULL),
  (1, 'DUAM XNAHT', NULL, NULL),
  (2, 'HACKEM MUCHE', 'FNORD', NULL),
  (3, 'DAIYEN FOOELS', 'ELBIB YLOH', 'GARVEN DEH'),
  (4, 'JUYED AWK YACC', 'FNORD', NULL),
  (5, 'FOOBIE BLETCH', NULL, NULL),
  (6, 'JUYED AWK YACC', 'FOOBIE BLETCH', NULL),
  (7, 'HACKEM MUCHE', 'FNORD', 'FOOBIE BLETCH'),
  (8, 'DAIYEN FOOELS', 'GARVEN DEH', 'ELBIB YLOH')

我如何找到 foo.ID 的值其中 Box1 的任意组合, Box2 , 和 Box3包含指定值？要找到的值的顺序无关紧要。结果中应显示包含比要查找的值多的值的行。例如

DECLARE @ArgValue varchar

SET @ArgValue = 'FOOBIE BLETCH' -- match 0, 5, 6, 7
SET @ArgValue = 'GARVEN DEH, DAIYEN FOOELS, ELBIB YLOH' -- match 3, 8
SET @ArgValue = 'FNORD, JUYED AWK YACC' -- match 4

假设(如有必要)存在存储过程 ArgVal_Split(@ArgVal varchar(max), @Delimiter char(1))可以提取子字符串并将它们作为单列表返回。

我的真实数据实际上比这复杂得多(一个表有 20 个可能匹配的不同列)，所以我正在寻找不涉及枚举列名的解决方案。

Answer 1

这个设计闻起来...

每当您想添加带有数字的列(经常看到:Telephone1、Telephone2...)时，您宁愿使用与 1:n 相关的边表!

但这仍然是可能的:

DECLARE @foo TABLE 
(
  ID int NOT NULL,
  Box1 varchar(50),
  Box2 varchar(50),
  Box3 varchar(50)
);

INSERT INTO @foo (ID, Box1, Box2, Box3) VALUES 
  (0, 'FOOBIE BLETCH', NULL, NULL),
  (1, 'DUAM XNAHT', NULL, NULL),
  (2, 'HACKEM MUCHE', 'FNORD', NULL),
  (3, 'DAIYEN FOOELS', 'ELBIB YLOH', 'GARVEN DEH'),
  (4, 'JUYED AWK YACC', 'FNORD', NULL),
  (5, 'FOOBIE BLETCH', NULL, NULL),
  (6, 'JUYED AWK YACC', 'FOOBIE BLETCH', NULL),
  (7, 'HACKEM MUCHE', 'FNORD', 'FOOBIE BLETCH'),
  (8, 'DAIYEN FOOELS', 'GARVEN DEH', 'ELBIB YLOH');

DECLARE @ArgValue VARCHAR(100);

--SET @ArgValue = 'FOOBIE BLETCH'; -- match 0, 5, 6, 7
SET @ArgValue = 'GARVEN DEH, DAIYEN FOOELS, ELBIB YLOH'; -- match 3, 8
--SET @ArgValue = 'FNORD, JUYED AWK YACC'; -- match 4


WITH DerivedTable AS
(
    SELECT x.value('.','nvarchar(max)') aS part
    FROM
    ( 
    SELECT CAST('<x>' + REPLACE(@ArgValue,', ','</x><x>') + '</x>' AS XML)
    ) AS tbl(Casted)
    CROSS APPLY Casted.nodes('/x') AS A(x)
)
SELECT ID
FROM @foo AS f
CROSS APPLY (VALUES(Box1),(Box2),(Box3)) AS boxes(box)
INNER JOIN DerivedTable AS dt ON box=part
GROUP BY ID
HAVING COUNT(ID)>=(SELECT COUNT(*) FROM DerivedTable)

一个简短的解释:

通过替换 ,使用 xml-tags 转换字符串 'GARVEN DEH, DAIYEN FOOELS, ELBIB YLOH'至 '<x>GARVEN DEH</x><x>DAIYEN FOOELS</x><x>ELBIB YLOH</x>' .这是 XML，可以很容易地用作派生表。

值得一提的是，您的字符串不应包含诸如 < 之类的禁止字符。 , >和 & .如果你需要这个，你会找到一个可靠的解决方案 here .

选择使用 CROSS APPLY与 VALUES , 一招 UNPIVOT并执行 INNER JOIN .只有结果是有效的，其中计数相同，因为搜索参数中有部分。