| _覓 | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁 |
|
FrontPage › UnPivot覓語
|
|
|
[edit]
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 企 貉, 貉, 貉朱 れ螳 煙 企襦 り 蠏碁Μ 譬讌 蟲譟一企. 觜蠏 企襦 覲 朱, 貉殊 豢螳る 企 蟲譟磯ゼ 覲蟆渚伎 . 蠍一ヾ 覓企 蠏碁襦願, 讌 貉殊 譟 訖企朱 企 蟲譟磯 譬讌 蟲譟一企.
--一危一煙ろ襴渚
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螳 企 谿企ゼ 覿襯手?
[edit]
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
*/
[edit]
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
...
*/
鏤
|
襷 願 蟇企 るΜ手 螳. るΜ螳 朱 轟 蟇企 讌 蟾. |