Contents

1 覓語-
2 覓語 覿
3 ろ 一危
4 SQL Server 2000 覯
5 SQL Server 2005 覯


1 覓語- #

谿 貉るる一 貎朱Μ 讌覓語 殊. 覘 給 蟇 伎 企瓦. 覓語襯 渚覃 れ螻 螳.
企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

2 覓語 覿 #

覓語 旧 り. 企C P貉殊 覲牛貉(覲牛)企. 讀, 'P1 螳螻 P4 螳 螳' 螳 轟 Row 轟 Row螳 螳る 蟆 覩誤螻 . 伎覃 '='螻 螳 一一 れ伎蠍 覓語 襷 'P1-P4' 螳 '=' るジ 一一 螳 る 企れ讌. 讀, 覩語 螳 襯 る. 讀, 企C C(p1, p2, oper) 螳 讓手讌蟇磯 '='一一襷 れ願 蟆曙磯 貉殊 2螳螳 1螳襯 螳語 . 蠏碁 貉殊 1螳朱 蟆 企襦 蟆 る 覩誤. 讀, 企 企 襷れ 覲伎ろ 覲旧′焔 螳譴 譬 蟆曙郁 蟆企.

3 ろ 一危 #

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
*/

4 SQL Server 2000 覯 #

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

5 SQL Server 2005 覯 #

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