你可以看到,这耗费了大量时间。 任何替代办法? 我用条款尝试使用集团各栏别,但没有结果。
select count(callid) ,
case
when callDuration > 0 and callDuration < 30 then 1
when callDuration >= 30 and callDuration < 60 then 2
when callDuration >= 60 and callDuration < 120 then 3
when callDuration >= 120 and callDuration < 180 then 4
when callDuration >= 180 and callDuration < 240 then 5
when callDuration >= 240 and callDuration < 300 then 6
when callDuration >= 300 and callDuration < 360 then 7
when callDuration >= 360 and callDuration < 420 then 8
when callDuration >= 420 and callDuration < 480 then 9
when callDuration >= 480 and callDuration < 540 then 10
when callDuration >= 540 and callDuration < 600 then 11
when callDuration >= 600 then 12
end as duration
from callmetatbl
where programid = 1001 and callDuration > 0
group by case
when callDuration > 0 and callDuration < 30 then 1
when callDuration >= 30 and callDuration < 60 then 2
when callDuration >= 60 and callDuration < 120 then 3
when callDuration >= 120 and callDuration < 180 then 4
when callDuration >= 180 and callDuration < 240 then 5
when callDuration >= 240 and callDuration < 300 then 6
when callDuration >= 300 and callDuration < 360 then 7
when callDuration >= 360 and callDuration < 420 then 8
when callDuration >= 420 and callDuration < 480 then 9
when callDuration >= 480 and callDuration < 540 then 10
when callDuration >= 540 and callDuration < 600 then 11
when callDuration >= 600 then 12
end
EDIT: I really meant to ask how to have a single case source, but case modifications are welcome anyway (although less useful because the intervals probably will be modified and might even be automatically generated).
正如一些人所认为的那样,“呼吁”的确是一个浮动,因此,一些列出的解决办法对我来说是站不住脚的,把价值观放在间隔之外。
经验教训:
寻找案件模式,以表示可能和值得减少这种情况
case when callDuration > 0 AND callDuration < 30 then 1 when callDuration > 600 then 12 else floor(callDuration/60) + 2 end end as duration1. 利用线性观点使案件有一个单一来源
select count(d.callid), d.duration from ( select callid , case when callDuration > 0 AND callDuration < 30 then 1 when callDuration > 600 then 12 else floor(callDuration/60) + 2 end end as duration from callmetatbl where programid = 1001 and callDuration > 0 ) d group by d.duration或使用共同表格表示
with duration_case as ( select callid , case when callDuration > 0 AND callDuration < 30 then 1 when callDuration > 600 then 12 else floor(callDuration/60) + 2 end end as duration from callmetatbl where programid = 1001 and callDuration > 0 ) select count(callid), duration from duration_case group by duration或者使用用户确定的职能(迄今没有实例:-)
或使用一张表和一张加入书
DECLARE @t TABLE(durationFrom float, durationTo float, result INT) --populate table with values so the query works select count(callid) , COALESCE(t.result, 12) from callmetatbl JOIN @t AS t ON callDuration >= t.durationFrom AND callDuration < t.durationTo where programid = 1001 and callDuration > 0
由于每个人和我的人都很难选择一个可接受的答案,因为许多人回答了问题的不同部分(我当时认为这是一个简单的问题,有直截了当的回答:-),令人担心混乱。