单个 CASE 语句中的多列 [英] Multiple columns within a single CASE statement

问题描述

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

I'm sure this has be covered many times so please pardon my repeat. I have a query that works but currently has 6 CASE statements within one select. Someone mentioned that it would be best optimized by putting all my WHEN conditions within a single CASE. However, I'm unable to achieve this

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

推荐答案

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

In your case, you can't put them all into one CASE, since the results all go into different columns of the select.

顺便说一句，你应该删除你的 ISNULL(region, '''') <>'''' 条件，因为它与 LEN(region) > 配对时是多余的5 条件.(当region为null时，LEN(region)也为null，NULL > 5为false.)

BTW, you should remove your ISNULL(region, '''') <> '''' condition, as it's redundant when paired with the LEN(region) > 5 condition. (When region is null, then LEN(region) is also null, and NULL > 5 is false.)

这篇关于单个 CASE 语句中的多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！