Contents

1 覓語 SQL覓語
2 願屋 覦


1 覓語 SQL覓語 #

#temp螳 覈 蟇 讌 讌襷, れ襦 4豌 襷 蟇 螳伎.
--drop table #temp
create table #temp(
	a varchar(20)
,	i int
,	ranking int
)

insert #temp(a, i) values('a', 1)
insert #temp(a, i) values('a', 2)
insert #temp(a, i) values('a', 3)
insert #temp(a, i) values('b', 10)
insert #temp(a, i) values('c', 5)
insert #temp(a, i) values('b', 7)
insert #temp(a, i) values('e', 8)

select * from #temp order by 1,2
/*
a                    i           ranking
-------------------- ----------- -----------
a                    1           NULL
a                    2           NULL
a                    3           NULL
b                    7           NULL
b                    10          NULL
c                    5           NULL
e                    8           NULL
*/

update a
set a.ranking = b.ranking
from #temp a 
	inner join (
		select
			a
		,	i
		,	rank() over(partition by a order by i) ranking 
		from #temp) b
	on a.a = b.a
	and a.i = b.i

select * from #temp order by 1,2	
/*
a                    i           ranking
-------------------- ----------- -----------
a                    1           1
a                    2           2
a                    3           3
b                    7           1
b                    10          2
c                    5           1
e                    8           1
*/

SQL 譟一語朱 Merge Join朱 襴覃伎 4豌 襷 蟇伎 1 . 2覯 れ . れ螻 螳 覦朱 1 れ朱 襴蟆 .

2 願屋 覦 #

update a
set ranking = up_ranking
from (
	select 
		a
	,	i
	,	ranking
	,	rank() over(partition by a order by i) up_ranking
	from #temp) a

螻 蟆 螻, 螻覩殊 り View 一一 殊企襦 襴 螳螻 覦語 覲語 朱 企瓦. 蠏碁磯 ろ 蟆 螳? ..