利用 Case When 的動態群組與 Join

朋友的問題

use tempdb

create table t(c1 int,c2 int,c3 int,c4 int,c5 int)
create table t2(c1 int,c2 int)

insert t values(1,2,3,4,5),(11,22,33,44,55),(111,2,3,4,5),(NULL,NULL,NULL,444,5)
insert t2 values(1,1),(2,2),(1,2)

declare @i int=0
select case when @i=0 then c1 end ,
case when @i=0 then c3 else c4 end cc,avg(c5) avgC5
from t
group by case when @i=0 then c1 end,case when @i=0 then c3 else c4 end

image

select * from t join t2 on t.c1=t2.c1 and case when @i=0 then t.c2 else t2.c2 end=t2.c2

image

 

set @i =1
select case when @i=0 then c1 end ,
case when @i=0 then c3 else c4 end cc,avg(c5) avgC5
from t
group by case when @i=0 then c1 end,case when @i=0 then c3 else c4 end

image

select * from t join t2 on t.c1=t2.c1

image

select * from t join t2 on t.c1=t2.c1 and case when @i=0 then t.c2 else t2.c2 end=t2.c2

image

drop table t
drop table t2

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: