Contents

1
2 ろ 一危
3 蠏 覦覯1
4 蠏 覦覯2
5 蟆磯



1 #

れ螻 螳 企 ikey覲 a, b, c 譴 螳 螳 谿城 蟆 覓語. , 螳 0覲企 貉れ . ikey 螻讌 朱, 螳 磯殊 1蟇伎 螻, 1襷 蟇伎 .
ikey        a           b           c
----------- ----------- ----------- -----------
244         382         961         368
275         623         372         947
212         148         744         466
783         75          479         678
301         931         631         998
569         872         154         435
363         654         257         388
437         655         109         327
382         149         534         725
846         471         106         187

企 貉, 貉, 貉朱 れ螳 煙 企襦 り 蠏碁Μ 譬讌 蟲譟一企. 觜蠏 企襦 覲 朱, 貉殊 豢螳る 企 蟲譟磯ゼ 覲蟆渚伎 . 蠍一ヾ 覓企 蠏碁襦願, 讌 貉殊 伎 訖企朱 企 蟲譟磯 譬讌 蟲譟一企.

2 ろ 一危 #

-- 一危  ろ襴渚
set nocount on
set statistics io on
 
if object_id('temp..#temp') is not null
	drop table #temp
 
create table #temp(
	ikey int
,	a int
,	b int
,	c int
)
go
 
declare
	@i int
,	@i_key int
,	@a int
,	@b int
,	@c int
set @i = 1
 
while(@i <= 100000)
begin
	set @i_key = replace(right(cast(rand() as varchar), 3) , '.', '')
	set @a = replace(right(cast(rand() as varchar), 3), '.', '')
	set @b =  replace(right(cast(rand() as varchar), 3), '.', '')
	set @c =  replace(right(cast(rand() as varchar), 3), '.', '')
 
	insert #temp values(@i_key, @a, @b, @c)
	set @i = @i + 1
end
 
create index idx on #temp(ikey)
go

3 蠏 覦覯1 #

一危 ろ襴渚碁ゼ ろ螻, ikey 豺企襴郁 螳 襷 蟆 覲伎.

select top 100 ikey, count(*) cnt from #temp group by ikey
order by 2 desc
/*
ikey        cnt
----------- -----------
804         145
935         141
687         140
918         140
754         138
207         137
588         137
*/

ikey = 804螳 145蟇伎朱 螳 襷. 所 螳覃 れ螻 螳 SQL 燕 蟆企. 蠏碁 螳 SQL 螳 一危磯ゼ 3覯企 曙伎 .

set statistics profile on
 
--SQL1: UNION ALL伎
select ikey, min(val) as min_val
from(
	select ikey, min(a) as val
	from #temp
	where a > 0 and ikey = 804
	group by ikey
	
	union all
	
	select ikey, min(b)
	from #temp
	where b > 0 and ikey = 804
	group by ikey
	
	union all
	
	select ikey,min(c)
	from #temp
	where c > 0 and ikey = 804
	group by ikey
    ) a
group by ikey
--蟆3, 朱Μ所鍵444, 覓朱Μ所鍵0

4 蠏 覦覯2 #

--SQL2: Cross Join伎
select
        ikey
,	min(
               case
                       when id = 1 and a > 0 then a
                       when id = 2 and b > 0 then b
                      when id = 3 and c > 0 then c
               end) min_val
from (
        select
               *
        from #temp
        where ikey = 804 ) a cross join (select 1 id union all select 2 union all select 3) b
group by ikey
--蟆1, 朱Μ所鍵148

5 蟆磯 #

SQL1 朱Μ 所鍵 444願, SQL2 148企. 讀, 148 * 3 = 444企襦 3覦一 朱Μ 所鍵 谿願 蟆企. 轟壱. 企 覦覲牛 3 曙朱蟾 襷企. 貎朱Μ襯 ろ貅 れ ろ螻 觜 觜蟲襯 企慨覃 蟆曙磯 SQL1 81%願, SQL2 19%企. 4覦 谿企. 襷 蟆曙 襷れ 襷 螳 螳 貎朱Μ襯 DBMS 襴磯る 覿 譬 蟆企. れ 1襷 覈 貎朱Μ襯 襴磯り 螳 覲伎. SQL1螻 SQL2螳 企 谿企ゼ 覿襯手? るジ れ 豌覿 殊 谿瑚蠍 覦.