_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › UnPivot覓語

Contents

1 蠍磯蓋 覓語
2 覓語2
3 願屋



1 蠍磯蓋 覓語 #

蟆:
- CPU:1.6GHz P4 覈覦
- RAM: 1GB
- DBMS: Microsoft SQL Server 2005 + SP2

れ螻 螳 企 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

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

--一危一煙ろ襴渚
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

一危 ろ襴渚碁ゼ ろ螻, 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, 覩碁Μ所鍵0, LOB 朱Μ所鍵0, LOB 覓朱Μ所鍵0, LOB 覩碁Μ所鍵0.
 
--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, 覓朱Μ所鍵0, 覩碁Μ所鍵0, LOB 朱Μ所鍵0, LOB 覓朱Μ所鍵0, LOB 覩碁Μ所鍵0.

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

2 覓語2 #

れ螻 螳 企 . 蟆郁骸襯 詞 貎朱Μ襯 讌覲企.
USE tempdb;

IF EXISTS(SELECT * FROM sysobjects 
   WHERE ID = (OBJECT_ID('#temp')) AND xtype = 'U')
DROP TABLE #temp
GO

CREATE TABLE #temp(
 [#] [tinyint] NULL,
 [1] [tinyint] NULL,
 [2] [tinyint] NULL,
 [3] [tinyint] NULL,
 [4] [tinyint] NULL,
 [5] [tinyint] NULL,
 [6] [tinyint] NULL,
 [7] [tinyint] NULL,
 [8] [tinyint] NULL,
 [9] [tinyint] NULL
)

INSERT INTO #temp VALUES(1, 8, 5, 2, 8, 6, 8, 9, 9, 1);
INSERT INTO #temp VALUES(2, 3, 8, 6, 4, 9, 3, 6, 9, 6);
INSERT INTO #temp VALUES(3, 2 , 8, 9, 3, 4, 3, 7, 4, 9);
INSERT INTO #temp VALUES(4, 1, 9, 4, 5, 7, 9, 9, 5, 6);
INSERT INTO #temp VALUES(5, 7, 6, 2, 9, 7, 9, 9, 6, 8);
INSERT INTO #temp VALUES(6, 5, 2, 9, 6, 5, 8, 6, 8, 9);
INSERT INTO #temp VALUES(7, 4, 4, 2, 5, 9, 5, 8, 6, 9);
INSERT INTO #temp VALUES(8, 5, 9, 9, 3, 7, 7, 6, 2, 6);
INSERT INTO #temp VALUES(9, 2, 3, 2, 7, 9, 6, 6, 2, 9);

/*
#    1    2    3    4    5    6    7    8    9
---- ---- ---- ---- ---- ---- ---- ---- ---- ----
1    8    5    2    8    6    8    9    9    1
2    3    8    6    4    9    3    6    9    6
3    2    8    9    3    4    3    7    4    9
4    1    9    4    5    7    9    9    5    6
5    7    6    2    9    7    9    9    6    8
6    5    2    9    6    5    8    6    8    9
7    4    4    2    5    9    5    8    6    9
8    5    9    9    3    7    7    6    2    6
9    2    3    2    7    9    6    6    2    9
*/

3 願屋 #

SQL Server 2005 伎 覯企朱 Pivot/UnPivot 企 . 伎 蠍磯蓋 Cross Join 伎伎 暑る 蟆..
with temp(no)
as
(
        select 1 no
        union all
        select no + 1 from temp
        where no + 1 <= 9
)--2005覯襷 . temp朱 企 0 ~ 9 螳  Dumy企企 螳覃 . 
select 
	[#] [Row number]
,	b.no [Column number]
,	case
		when b.no=1 then [1]
		when b.no=2 then [2]
		when b.no=3 then [3]
		when b.no=4 then [4]
		when b.no=5 then [5]
		when b.no=6 then [6]
		when b.no=7 then [7]
		when b.no=8 then [8]
		when b.no=9 then [9]
	end	[Value]
from #temp a
	cross join temp b
/*
Row number Column number Value
---------- ------------- -----
1          1             8
2          1             3
3          1             2
4          1             1
5          1             7
6          1             5
7          1             4
...
*/
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:52

伎觜螳 企Μ螻 . 轟 覦 螳 一一 ク. 蠏瑚朱 豢覿.蟲讌蟲讌蟆 觜螳 る蟲! 企 襷 覓伎 螳. 觜 蟲襴 覦 蟆一 襷襦 讌 伎Ц 觜 覯 る所 企Μ蟲謂螻 襷讌 覈詩螳. <<襷 覲牛伎 255螳讌 伎手鍵>> ()