1 문제-

2 문제 분석

3 테스트 데이터

4 SQL Server 2000 버전

5 SQL Server 2005 버전

심심하던 차에 커뮤니티에 쿼리 질문이 올라왔다. 뭐 여러 답변이 있었는데 걍 심심해서 해봤다. 문제를 요약하면 다음과 같다.

테이블A
p
--------------------
P1
P2
P3
P4

테이블B
p                    val
-------------------- -----------
P1                   100
P2                   20
P4                   200

테이블C
pp
--------------------
P1 = P4


원하는결과
p          val
---------- -----------
P1         200
P2         20
P3         0
P4         400

질문원본: http://devpia.com/Maeul/Contents/Detail.aspx?BoardID=41&MAEULNO=17&no=2059&ref=2059&page=1

문제의 핵심은 역시 설계다. 테이블C의 P컬럼은 복합컬럼(복합속성)이다. 즉, 'P1의 값과 P4의 값이 같다'와 같이 특정 Row와 특정 Row가 같다는 것을 의미하고 있다. 또한 더욱 확대 해석하면 '='과 같은 연산자도 들어있기 때문에 만약 'P1-P4'와 같이 '='이 아닌 다른 연산자도 값에 포함될 수 있다면 더욱 어려워진다. 즉, 의미적인 원자값이 아닐 확률이 다분하다. 즉, 테이블C는 C(p1, p2, oper)와 같이 쪼개지거나 '='연산자만 들어갈 경우는 컬럼이 2개가 아니라 1개를 가져야 한다. 그러나 컬럼이 1개라는 것은 테이블로써의 자격이 상실됨을 의미한다. 즉, 없어도 될 테이블을 만들어 정보시스템의 복잡성만 가중시킨 안 좋은 경우가 된 것이다.

use tempdb
go

create table A(p varchar(20))
create table B(p varchar(20), val int)
create table C(pp varchar(20))

insert A values('P1')
insert A values('P2')
insert A values('P3')
insert A values('P4')

insert B values('P1', 100)
insert B values('P2', 20)
insert B values('P4', 200)

insert C values('P1 = P4')

select * from A
select * from B
select * from C
*/

select 
	convert(varchar(10), A.p) p
,	isnull(sum(B.val), 0) val
from (
	select p from A
	union all
	select 
		case 
			when T.seq = 1 then left(pp, charindex('=', pp) - 1) 
			else substring(pp, charindex('=', pp) + 1, 8000)
		end p
	from (
		select
			replace(pp, ' ', '') pp
		from C) C cross join (select 1 seq union all select 2) T) A left outer join B
on A.p = B.p
group by A.p
order by A.p

with cte (p) 
as
(
select 
	case 
		when T.seq = 1 then left(pp, charindex('=', pp) - 1) 
		else substring(pp, charindex('=', pp) + 1, 8000)
	end p
from (
	select
		replace(pp, ' ', '') pp
	from C) C cross join (select 1 seq union all select 2) T
)
select 
	convert(varchar(10), A.p) p
,	isnull(sum(B.val), 0) val
from (
	select p from A
	union all
	select p from cte) A left outer join B
on A.p = B.p
group by A.p
order by A.p