sql-server - 如何在 SQL Server 上的 CASE WHEN 中选择多个列？

Question

我已经广泛搜索了该网站，但找不到解决方案。

这是我的查询示例:

SELECT 
   ActivityID,

   Hours = (CASE 
                WHEN ActivityTypeID <> 2 THEN
                     FieldName = (Some Aggregate Sub Query),
                     FieldName2 = (Some other aggregate sub query)
                WHEN ActivityTypeID = 2 THEN
                     FieldName = (Some Aggregate Sub Query with diff result),
                     FieldName2 = (Some Other Aggregate Sub Query with diff result)
           END)

显然我遗漏了很多查询，我只是想看看是否可能。

我知道我可能可以只做两次“CASE”，但我想我会问......

Answer 1

问题是 CASE 语句无法按照您尝试使用的方式工作。您只能使用它来切换查询中一个字段的值。如果我明白你想要做什么，你可能需要这个:

SELECT 
   ActivityID,
   FieldName = CASE 
                  WHEN ActivityTypeID <> 2 THEN
                      (Some Aggregate Sub Query)
                  ELSE
                     (Some Aggregate Sub Query with diff result)
               END,
   FieldName2 = CASE
                  WHEN ActivityTypeID <> 2 THEN
                      (Some Aggregate Sub Query)
                  ELSE
                     (Some Aggregate Sub Query with diff result)
               END