如何实现仅匹配全部动态参数的动物名称查询存储过程

需求回顾 #

我们有一个animal表，记录了动物及其对应的动作：

id     | action
------------------
duck   | cuack
duck   | fly
duck   | swim
pelican| fly
pelican| swim

需要创建一个存储过程GuessAnimalName，接收一个逗号分隔的动作字符串参数，规则是：

• 只有当传入的所有动作恰好是某动物的全部动作时，才返回该动物名称
• 示例：

  EXEC GuessAnimalName 'cuack,fly,swim' -- 返回 duck
  EXEC GuessAnimalName 'fly,swim'      -- 返回 pelican
  EXEC GuessAnimalName 'fly'           -- 返回 No results
  

你的当前实现问题 #

你模拟参数的代码返回了duck和pelican，但预期只有duck——问题出在HAVING子句的条件上：当前条件只判断了「动物匹配到的动作数等于它自身的总动作数」，但没判断「匹配到的动作数等于传入的参数总数」。这就导致pelican虽然没匹配到cuack，但它匹配到的2个动作正好是它自己的全部动作，所以被错误选中了。

修正方案 #

第一步：修正模拟代码的逻辑 #

先调整你的测试代码，把条件补全：

DECLARE @animal AS TABLE ( [id] nvarchar(8), [action] nvarchar(16) )
INSERT INTO @animal VALUES('duck','cuack')
INSERT INTO @animal VALUES('duck','fly')
INSERT INTO @animal VALUES('duck','swim')
INSERT INTO @animal VALUES('pelican','fly')
INSERT INTO @animal VALUES('pelican','swim')

-- Parameter simulation
DECLARE @params AS TABLE ( [action] nvarchar(16) )
INSERT INTO @params VALUES('cuack')
INSERT INTO @params VALUES('fly')
INSERT INTO @params VALUES('swim')

-- 统计传入参数的数量
DECLARE @paramCount INT = (SELECT COUNT(DISTINCT [action]) FROM @params)

SELECT a.[id]
FROM @animal a
INNER JOIN @params p ON a.[action] = p.[action]
GROUP BY a.[id]
HAVING 
  -- 条件1：传入的所有动作都被该动物匹配到（匹配数等于参数总数）
  COUNT(DISTINCT p.[action]) = @paramCount
  -- 条件2：该动物的所有动作都在传入参数中（没有额外动作）
  AND COUNT(DISTINCT a.[action]) = (SELECT COUNT(DISTINCT [action]) FROM @animal WHERE id = a.id)

这段代码会正确返回duck，符合预期。

第二步：封装为存储过程 #

接下来把逻辑封装成可接收逗号分隔参数的存储过程，需要先把传入的字符串拆分成动作列表：

CREATE PROCEDURE GuessAnimalName
  @actionList NVARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;

  -- 临时表存储拆分后的动作
  DECLARE @params TABLE ( [action] NVARCHAR(16) )
  -- 拆分逗号分隔的字符串（过滤空值）
  INSERT INTO @params
  SELECT TRIM(value) FROM STRING_SPLIT(@actionList, ',') WHERE TRIM(value) <> ''

  -- 统计参数数量
  DECLARE @paramCount INT = (SELECT COUNT(DISTINCT [action]) FROM @params)

  -- 如果没有传入有效参数，直接返回提示
  IF @paramCount = 0
  BEGIN
    SELECT 'No results' AS Result
    RETURN
  END

  -- 查询符合条件的动物
  SELECT id AS Result
  FROM animal
  INNER JOIN @params p ON animal.[action] = p.[action]
  GROUP BY id
  HAVING 
    COUNT(DISTINCT p.[action]) = @paramCount
    AND COUNT(DISTINCT animal.[action]) = (SELECT COUNT(DISTINCT [action]) FROM animal WHERE id = animal.id)

  -- 如果没有匹配结果，返回提示
  IF @@ROWCOUNT = 0
  BEGIN
    SELECT 'No results' AS Result
  END
END

验证测试 #

执行以下语句测试：

EXEC GuessAnimalName 'cuack,fly,swim' -- 返回 duck
EXEC GuessAnimalName 'fly,swim'      -- 返回 pelican
EXEC GuessAnimalName 'fly'           -- 返回 No results

完全符合你的需求。

内容的提问来源于stack exchange，提问作者Máster