sql - COUNT 选择和局部变量存储

Question

我需要找到与他们关联的客户数量最少的人的 ID，以便将当前的新客户分配给该员工。

我使用以下内容来建立该工作人员的 ID，如下所示；

    SELECT TOP(1) JA.judgeID, COUNT(JA.judgeId) AS COUNT
    FROM recJudgeAssignment AS JA
    LEFT JOIN recEntrantStatus AS ES
    ON JA.bandId = ES.entrantId
    GROUP BY JA.judgeId, ES.roundId
    HAVING ES.roundId = @round
    ORDER BY COUNT

但我需要将其捕获为局部变量以在下一条语句中使用；

    UPDATE recJudgeAssignment
    SET judgeId = @judge
    WHERE roundId = @round
    AND bandId = @entrantId

如何在第一个选择中捕获 judgeId？通常我会做类似的事情；

    SELECT @judge =(SELECT TOP(1) JA.judgeID, COUNT(JA.judgeId) AS COUNT
    FROM recJudgeAssignment AS JA
    LEFT JOIN recEntrantStatus AS ES
    ON JA.bandId = ES.entrantId
    GROUP BY JA.judgeId, ES.roundId
    HAVING ES.roundId = @round
    ORDER BY COUNT)

这当然不起作用，因为选择返回 judgeId 和 COUNT。我宁愿不必将第一次选择的结果发送回网页，然后在单独的 web>db 事务中执行后续更新语句。

所以...任何人都可以提供任何帮助甚至补救措施，我们将不胜感激。

提前致谢!

Answer 1

-- Query the DB for your variables
-- (I have also moved the HAVING clause to a WHERE clause.)
------------------------------------------------------------
SELECT TOP(1)
  @judgeID = JA.judgeID,
  @count   = COUNT(JA.judgeId)
FROM
  recJudgeAssignment AS JA
LEFT JOIN
  recEntrantStatus AS ES
    ON JA.bandId = ES.entrantId
WHERE
  ES.roundId = @round
GROUP BY
  JA.judgeId, ES.roundId
ORDER BY
  COUNT

-- Update the DB
------------------------------------------------------------
UPDATE
  recJudgeAssignment
SET
  judgeId = @judgeID
WHERE
      roundId = @round
  AND bandId  = @entrantId

-- Return the variables to the client
------------------------------------------------------------
SELECT
  @judgeID     AS judgeID,
  @count       AS count