Contents

1 覓語1
2 1
3 2


1 覓語1 #

企....
col1 . col2
------------
A       a1
A       b1
B       a1
C       b1
D       a1
D       b1
D       c1


企蟆  譟郁唄朱 (a1, b1)  語 a1 螻 b1  螻  
A - a1, A - b1, D-a1, D-b1 襷 螳語り 苦給. B - a1, C - b1, D - c1  襦磯 觜手


れ襦 譟郁唄 (a1, b1)   るジ 企 譟壱 螳.
磯 蠏碁9 讌伎 る 企至 伎 讌...碁朱 覿襴暑.
* 讌覓 覲: http://www.devpia.com/MAEUL/Contents/Post.aspx?BoardID=41&MAEULNO=17&mode=Reply&no=7099&page=1
* 蟆郁骸 覈讌 讌襷, 企 手 螳螻..

2 1 #

--drop table #temp
select 'A' col1, 'a1' col2 into #temp union all
select 'A' col1, 'b1' col2 union all
select 'B' col1, 'a1' col2 union all
select 'C' col1, 'b1' col2 union all
select 'D' col1, 'a1' col2 union all
select 'D' col1, 'b1' col2 union all
select 'D' col1, 'c1' col2 

--襾 豢 企蟆..
;with rs
as
(
	select 
		col1
	,	min(case when seq = 1 then col2 end)col2
	,	min(case when seq = 2 then col2 end) col3
	from #temp a
		cross join (select 1 seq union all select 2) b
	where col2 in ('a1', 'b1')
	group by
		col1
)
select a.*
from #temp a
	inner join rs b
		on a.col1 = b.col1
		and a.col2 in (b.col2, b.col3)
where b.col2 is not null
and b.col3 is not null

3 2 #

--襾.. 豢 企蟆   .. 企 3覯企 曙..
select 
	c.*
from #temp a
	inner join #temp b
		on a.col1 = b.col1
	inner join #temp c
		on a.col1 = c.col1
		and c.col2 in (a.col2, b.col2)
where a.col2 = 'a1'
and b.col2 = 'b1'