sql - 如何在 MS SQL 中的 CASE 条件中编写选择查询

我有一个 Sql 查询。它没有任何编译错误，但在运行时显示Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

 SELECT a.UserID FROM USERGROUPS a WHERE a.GroupID IN
                      (CASE WHEN @group_id IS NULL THEN (select groupid from usergroups) ELSE
                       CASE WHEN @group_id=0 THEN (select groupid from usergroups  where userid = @userid)ELSE
                      @group_id END END )

提前致谢。

最佳答案

试试这个:

SELECT a.UserID
  FROM USERGROUPS a
  WHERE
    (@groupid is NUll and a.GroupID IN   (select groupid from usergroups))
     or (@groupid = 0 and a.GroupID IN  (select groupid from usergroups  where (userid = @userid)))
      or a.GroupID IN  (@groupid)

关于sql - 如何在 MS SQL 中的 CASE 条件中编写选择查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/7105530/