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

Question

我有一个 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 )

提前致谢。

Answer 1

试试这个:

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)