| _覓 | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁 |
|
FrontPage › CASEWHEN
|
|
|
case when 覓語 れ螻 螳 企.
case when <= 10 then '1 ~ 10' when <= 20 then '11 ~ 20' else '21 ~ end 蠏碁 企蟆 . 狩襾殊 覓語螳 蟇一 .
襷 願 蠑語螻, 譟郁企朱 焔レ る慨螻 る 一危一 覿襯 螻ろ伎 . 襯 れ,
drop table #temp
create table #temp(num int)
go
insert #temp
select top 10000000 1
from master..spt_values a
cross join master..spt_values b
cross join master..spt_values c
cross join master..spt_values d
cross join master..spt_values e
go
insert #temp
select top 5000000 2
from master..spt_values a
cross join master..spt_values b
cross join master..spt_values c
cross join master..spt_values d
cross join master..spt_values e
go
insert #temp
select top 1000000 3
from master..spt_values a
cross join master..spt_values b
cross join master..spt_values c
cross join master..spt_values d
cross join master..spt_values e
go
select
num
, count(*) cnt
from #temp
group by
num
order by 1
select 蟆郁骸
num cnt ----------- ----------- 1 10000000 2 5000000 3 1000000 覿襯 螻ろ伎 譟郁唄 覦一 覃 所 焔 レ 蠍磯 .
譟郁唄 覲蟆渚 100 ろ.
--drop table #rs
create table #rs(
grp char(1)
, cnt int
)
--drop table #stat
create table #stat(method int, exec_time int)
set nocount on
set statistics io off
set statistics time off
--覦覯1
declare
@i int = 1
, @begin_dt datetime
, @end_dt datetime
truncate table #rs;
while(@i <= 100)
begin
set @begin_dt = getdate()
insert #rs
select
case
when num = 1 then 'a'
when num = 2 then 'b'
else 'c'
end
, count(*) cnt
from #temp
group by
case
when num = 1 then 'a'
when num = 2 then 'b'
else 'c'
end
set @end_dt = getdate()
insert #stat
select 1, datediff(ms, @begin_dt, @end_dt)
set @i += 1
end
go
--覦覯2
declare
@i int = 1
, @begin_dt datetime
, @end_dt datetime
truncate table #rs;
while(@i <= 100)
begin
set @begin_dt = getdate()
insert #rs
select
case
when num = 3 then 'c'
when num = 2 then 'b'
else 'a'
end
, count(*) cnt
from #temp
group by
case
when num = 3 then 'c'
when num = 2 then 'b'
else 'a'
end
set @end_dt = getdate()
insert #stat
select 2, datediff(ms, @begin_dt, @end_dt)
set @i += 1
end
go
豸′ 企慨覃..
select
method 覦覯
, avg(exec_time) 蠏
, stdev(exec_time) 譴ｸ谿
from #stat
group by
method
覦覯2螳 譟郁 ろ螳 蠍 蟆 覲 .
覦覯 蠏 譴ｸ谿 ----------- ----------- ---------------------- 1 507 143.08132144153 2 521 123.3129123143 num=1 企豪 1豌襷 蟇伎願, num=3 1覦焔 蟇伎企. 蠏碁覩襦 螳 覃 豌 覯讌 when num = 1 then 'a' 覿覿 蟇碁Μ蟆 .
case
when num = 1 then 'a' --------(1)
when num = 2 then 'b'
else 'c' --------(3)
end
讌襷, 螳危覃 num=1 企豪 1豌襷 蟇伎 譟郁唄 螳螳 else蟾讌 企り . 讀, if襯 覈 覯 磯 谿企.
case
when num = 3 then 'c'
when num = 2 then 'b'
else 'a'
end
鏤
|
讌 蟆 螳 伎れ . (企Π) |