如何制作一个“两个依赖参数”的SQL存储过程

I just can't figure it out, what I mean by two dependents parameters is this :

我只是无法弄明白，我的意思是两个dependents参数是这样的：

Suppose I have records like these :

假设我有这样的记录：

ID    Letter     Number
-----------------------
23    A          1
23    A          2
23    B          1
23    B          2
81    A          1
81    B          2

The user is to input this :

用户输入：

First parameter : A,B
第一个参数：A，B
Second parameter : 1,2
第二个参数：1,2

Then only ID 23 would be returned, because it's the only one that respect all these conditions :

然后只返回ID 23，因为它是唯一一个尊重所有这些条件的人：

A1, A2, B1, B2

A1，A2，B1，B2

Every time I tried some query, 81 was returned...

每次我尝试一些查询，都会返回81 ...

In the context of my question it would be quick enough to make 4 conditions like :

在我的问题的背景下，它将足够快地制定4个条件，如：

A and 1, A and 2, B and 1, B and 2

But imagine if i have 16 * 16 ...

但想象一下，如果我有16 * 16 ......

It would be extremely long to write them all...

把它们全部写下来会非常漫长......

1 个解决方案

#1

You can use COUNT DISTINCT in the HAVING clause:

您可以在HAVING子句中使用COUNT DISTINCT：

SELECT Id
FROM #Tbl
WHERE
    Letter IN('A', 'B')
    AND Number IN(1, 2)
GROUP BY Id
HAVING
    COUNT(DISTINCT Letter) = 2
    AND COUNT(DISTINCT Number) = 2;

For more dynamic approach, you can put the criteria in table variables:

对于更动态的方法，您可以将条件放在表变量中：

DECLARE @Letters TABLE(Letter CHAR(1));
DECLARE @Numbers TABLE(Number INT);

INSERT INTO @Letters VALUES ('A'), ('B');
INSERT INTO @Numbers VALUES (1), (2);

WITH CteCross(Letter, Number) AS(
    SELECT Letter, Number 
        FROM @Letters
        CROSS JOIN @Numbers
)
SELECT t.Id
FROM #Tbl t
INNER JOIN CteCross cc
    ON cc.Letter = t.Letter
    AND cc.Number = t.Number
GROUP BY t.Id
HAVING COUNT(*) = (SELECT COUNT(*) FROM CteCross);

ONLINE DEMO

在线演示

#1