T-SQL:如何在不使用游标的情况下查找匹配的记录

Question

我有两个表: ItemCriteria和Item

ItemCriteria具有以下字段

ID
Criteria1
Criteria2
Criteria3
Criteria4
Criteria5

项目具有除ID字段之外的相同字段

我需要在Item表中查找具有1个或多个匹配条件字段(不包括NULLS)的所有项目，并返回匹配的数量。

例如，如果我在ItemCriteria中有如下所示的记录：

+----+-----------+-----------+-----------+-----------+-----------+
| ID | Criteria1 | Criteria2 | Criteria3 | Criteria4 | Criteria5 |
+----+-----------+-----------+-----------+-----------+-----------+
|  1 | NULL      | AAB       | AAC       | NULL      | NULL      |
|  2 | AAB       | NULL      | NULL      | NULL      | NULL      |
|  3 | NULL      | AAC       | NULL      | NULL      | NULL      |
|  4 | AAA       | NULL      | AAC       | NULL      | NULL      |
|  5 | BBB       | AAB       | NULL      | NULL      | NULL      |
+----+-----------+-----------+-----------+-----------+-----------+

我在Item中有以下几行

+-----------+-----------+-----------+-----------+-----------+
| Criteria1 | Criteria2 | Criteria3 | Criteria4 | Criteria5 |
+-----------+-----------+-----------+-----------+-----------+
| AAA       | AAB       | AAC       | NULL      | NULL      |
| AAB       | AAC       | NULL      | NULL      | NULL      |
+-----------+-----------+-----------+-----------+-----------+

我希望结果是：

+-----------+-----------+-----------+-----------+-----------+------------------+----------------+
| Criteria1 | Criteria2 | Criteria3 | Criteria4 | Criteria5 | MatchingCriteria | ItemCriteriaID |
+-----------+-----------+-----------+-----------+-----------+------------------+----------------+
| AAA       | AAB       | AAC       | NULL      | NULL      |                2 |              1 |
| AAB       | AAC       | NULL      | NULL      | NULL      |                1 |              2 |
| AAB       | AAC       | NULL      | NULL      | NULL      |                1 |              3 |
| AAA       | AAB       | AAC       | NULL      | NULL      |                2 |              4 |
| AAA       | AAB       | AAC       | NULL      | NULL      |                1 |              5 |
+-----------+-----------+-----------+-----------+-----------+------------------+----------------+

目前，我有一个存储过程，它在ItemCriteria上打开一个游标并遍历条目，然后为每个条目创建一个执行的动态SQL语句。查询的结果被插入到临时表中。完成后，将处理临时表中的值，并将结果返回给调用者。

当涉及大量记录时，这是非常慢的。有没有更好/更快的方法来做这件事？

Answer 1

如果我正确地理解了需求，那么它就相当简单。您可以在联接中使用多个ORed条件。

declare @ItemCriteria table (id int, criteria1 char(3), criteria2 char(3), criteria3 char(3), criteria4 char(3), criteria5 char(3))
declare @Item table (criteria1 char(3), criteria2 char(3), criteria3 char(3), criteria4 char(3), criteria5 char(3))

insert @ItemCriteria values 
    (1 , NULL, 'AAB', 'AAC', NULL, NULL),
    (2 ,'AAB', NULL, NULL, NULL, NULL),
    (3 , NULL, 'AAC', NULL, NULL, NULL),
    (4 ,'AAA', NULL, 'AAC', NULL, NULL),
    (5 ,'BBB', 'AAB', NULL, NULL, NULL)

insert @item values 
    ('AAA', 'AAB', 'AAC', NULL, NULL),
    ('AAB', 'AAC', NULL, NULL, NULL)

select
    coalesce(i.criteria1, ic.criteria1) as Criteria1,
    coalesce(i.criteria2, ic.criteria2) as Criteria2,
    coalesce(i.criteria3, ic.criteria3) as Criteria3,
    coalesce(i.criteria4, ic.criteria4) as Criteria4,
    coalesce(i.criteria5, ic.criteria5) as Criteria5,
    case when i.criteria1 = ic.criteria1 then 1 else 0 end +
        case when i.criteria2 = ic.criteria2 then 1 else 0 end +
        case when i.criteria3 = ic.criteria3 then 1 else 0 end +
        case when i.criteria4 = ic.criteria4 then 1 else 0 end +
        case when i.criteria5 = ic.criteria5 then 1  else 0 end as MatchingCriteria,
    ic.id as ItemCriteriaID
from
    @ItemCriteria ic
        left join @Item i on i.criteria1 = ic.criteria1 
                          or i.criteria2 = ic.criteria2 
                          or i.criteria3 = ic.criteria3 
                          or i.criteria4 = ic.criteria4 
                          or i.criteria5 = ic.criteria5

Answer 2

在这里试试这个：

IF OBJECT_ID('tempdb..#ItemCriteria') IS NOT NULL
    DROP TABLE #ItemCriteria
IF OBJECT_ID('tempdb..#Item') IS NOT NULL
    DROP TABLE #Item

CREATE TABLE #ItemCriteria
(
    ID INT,
    Criteria1 VARCHAR(5),
    Criteria2 VARCHAR(5),
    Criteria3 VARCHAR(5),
    Criteria4 VARCHAR(5),
    Criteria5 VARCHAR(5),
)

INSERT INTO #ItemCriteria
VALUES  (1,NULL,'AAB','AAC',NULL,NULL),
        (2,'AAB',NULL,NULL,NULL,NULL),
        (3,NULL,'AAC',NULL,NULL,NULL),
        (4,'AAA',NULL,'AAC',NULL,NULL),
        (5,'BBB','AAB',NULL,NULL,NULL);

CREATE TABLE #Item
(
    Criteria1 VARCHAR(5),
    Criteria2 VARCHAR(5),
    Criteria3 VARCHAR(5),
    Criteria4 VARCHAR(5),
    Criteria5 VARCHAR(5),
);

INSERT INTO #Item
VALUES  ('AAA','AAB','AAC',NULL,NULL),
        ('AAB','AAC',NULL,NULL,NULL);

好了，现在我已经设置了表，下面是条件匹配。

 SELECT A.*,
        CASE WHEN A.Criteria1 = B.Criteria1 THEN 1 ELSE 0 END
        + CASE WHEN A.Criteria2 = B.Criteria2 THEN 1 ELSE 0 END
        + CASE WHEN A.Criteria3 = B.Criteria3 THEN 1 ELSE 0 END
        + CASE WHEN A.Criteria4 = B.Criteria4 THEN 1 ELSE 0 END
        + CASE WHEN A.Criteria5 = B.Criteria5 THEN 1 ELSE 0 END
        AS MatchingCriteria,
        B.ID AS ItemCriteriaID
FROM #Item A
INNER JOIN #ItemCriteria B
ON  A.Criteria1 = B.Criteria1
    OR A.Criteria2 = B.Criteria2
    OR A.Criteria3 = B.Criteria3
    OR A.Criteria4 = B.Criteria4
    OR A.Criteria5 = B.Criteria5
ORDER BY B.ID

结果：

Criteria1 Criteria2 Criteria3 Criteria4 Criteria5 MatchingCriteria ItemCriteriaID
--------- --------- --------- --------- --------- ---------------- --------------
AAA       AAB       AAC       NULL      NULL      2                1
AAB       AAC       NULL      NULL      NULL      1                2
AAB       AAC       NULL      NULL      NULL      1                3
AAA       AAB       AAC       NULL      NULL      2                4
AAA       AAB       AAC       NULL      NULL      1                5