sql - 单个 CASE 语句中的多列

Question

我相信这已经被覆盖了很多次，所以请原谅我的重复。我有一个有效的查询，但目前在一个选择中有 6 个 CASE 语句。有人提到最好将我所有的 WHEN 条件放在一个 CASE 中进行优化。但是，我无法实现这一目标

select right(RTRIM(region),5) as cell_id, 
     sum(CASE WHEN LEFT(cparty,3) in ('999','998','997') THEN chargeduration/60 else 0 END) AS OnNet_Minutes,
     sum(CASE WHEN LEFT(cparty,3) in ('996','995') THEN chargeduration/60 else 0 END) AS OffNet_C_Minutes,
     sum(CASE WHEN LEFT(cparty,3) in ('994','993','992') THEN chargeduration/60 else 0 END) AS OffNet_A_Minutes,
     sum(CASE WHEN LEFT(cparty,3) in ('991','990') THEN chargeduration/60 else 0 END) AS OffNet_S_Minutes,
     sum(CASE WHEN LEFT(cparty,2) = '00' THEN chargeduration/60 else 0 END) AS OffNet_T_Minutes,
     sum(CASE WHEN len(cparty) < 6 and LEFT(cparty,1) <> 0 THEN chargeduration/60 else 0 END) AS SC_Minutes            
  from August.dbo.cdr20130818 
  where CHARGEDURATION > 0 and ISNULL(region,'''')<>'''' and LEN(region) > 5
group by right(RTRIM(region),5)
order by right(RTRIM(region),5) asc

Answer 1

在你的情况下，你不能把它们都放在一个 CASE ，因为结果都进入了选择的不同列。

顺便说一句，你应该删除你的 ISNULL(region, '''') <> ''''条件，因为它与 LEN(region) > 5 配对时是多余的健康)状况。 (当 region 为空时，则 LEN(region) 也为空，而 NULL > 5 为假。)