有一个表和两个mast1， mast2
tb1
CODE1 CODE2 CODE3
PS10001 PS10001 PD10001
PS10002 PS10004 PD10002
PS10003 PS10005 PD10003
mast1
CODE NEW_CODE
PS10001 DS10001
PS10002 DS10002
mast2
CODE NEW_CODE
PD10001 DD10001
想在view里面将CODE1和CODE2转换成NEW_CODE,如果没有对应的NEW_CODE，
就将原来的code写在相应的列里面，然后在后面加上一个flag
view1
CODE1 CODE2 CODE3 CD_CNV_STS
DS10001 DS10001 DD10001
DS10002 PS10004 PD10002 1
PS10003 PS10005 PD10003 1
请大家伸出援手，帮帮忙
------解决方案--------------------
Create Table tb1
(CODE1 Varchar(10),
CODE2 Varchar(10),
CODE3 Varchar(10))
Insert tb1 Select 'PS10001 ', 'PS10001 ', 'PD10001 '
Union All Select 'PS10002 ', 'PS10004 ', 'PD10002 '
Union All Select 'PS10003 ', 'PS10005 ', 'PD10003 '
Create Table mast1
(CODE Varchar(10),
NEW_CODE Varchar(10))
Insert mast1 Select 'PS10001 ', 'DS10001 '
Union All Select 'PS10002 ', 'DS10002 '
Create Table mast2
(CODE Varchar(10),
NEW_CODE Varchar(10))
Insert mast2 Select 'PD10001 ', 'DD10001 '
GO
Create View V_TEST
As
Select
IsNull(B.NEW_CODE, CODE1) As CODE1,
IsNull(C.NEW_CODE, CODE2) As CODE2,
IsNull(D.NEW_CODE, CODE3) As CODE3,
(Case When B.NEW_CODE Is Null Or C.NEW_CODE Is Null Or D.NEW_CODE Is Null Then 1 Else 0 End) As CD_CNV_STS
From
tb1 A
Left Join
mast1 B
On A.CODE1 = B.CODE
Left Join
mast1 C
On A.CODE2 = C.CODE
Left Join
mast2 D
On A.CODE3 = D.CODE
GO
Select * From V_TEST
GO
Drop Table tb1, mast1, mast2
Drop View V_TEST
GO
--Result
/*
CODE1 CODE2 CODE3 CD_CNV_STS
DS10001 DS10001 DD10001 0
DS10002 PS10004 PD10002 1
PS10003 PS10005 PD10003 1
*/