--建立测试环境 Create table a (cInvCode Varchar(10), Name Varchar(10) )Create table b (Code Varchar(2)) GO --插入数据 Insert a Values('AA0001','KKD') Insert a Values('AA0002','JHG') Insert a Values('AA0003','JUY') Insert a Values('BB0001','LOJ') Insert a Values('BB0002','JTF')Insert b Values('AA') Insert b Values('BB') GO Create View [list] AS Select B.*,A.* from B Left Join A On B.Code=Left(A.cInvCode,2) GO --测试 Select * from list --删除测试环境 Drop table a,b Drop View list --结果 /* Code cInvCode Name AA AA0001 KKD AA AA0002 JHG AA AA0003 JUY BB BB0001 LOJ BB BB0002 JTF */
错了,重写。--建立测试环境 Create table a (cInvCode Varchar(10) )Create table b (Code Varchar(2), Name Varchar(10)) GO --插入数据 Insert a Values('AA0001') Insert a Values('AA0002') Insert a Values('AA0003') Insert a Values('BB0001') Insert a Values('BB0002')Insert b Values('AA','KKD') Insert b Values('BB','JHG') GO Create View [list] AS Select B.*,A.* from B Left Join A On B.Code=Left(A.cInvCode,2) GO --测试 Select * from list --删除测试环境 Drop table a,b Drop View list --结果 /* Code cInvCode Name AA KKD AA0001 AA KKD AA0002 AA KKD AA0003 BB JHG BB0001 BB JHG BB0002 */
Create table a
(cInvCode Varchar(10),
Name Varchar(10)
)Create table b
(Code Varchar(2))
GO
--插入数据
Insert a Values('AA0001','KKD')
Insert a Values('AA0002','JHG')
Insert a Values('AA0003','JUY')
Insert a Values('BB0001','LOJ')
Insert a Values('BB0002','JTF')Insert b Values('AA')
Insert b Values('BB')
GO
Create View [list]
AS
Select B.*,A.* from B Left Join A On B.Code=Left(A.cInvCode,2)
GO
--测试
Select * from list
--删除测试环境
Drop table a,b
Drop View list
--结果
/*
Code cInvCode Name
AA AA0001 KKD
AA AA0002 JHG
AA AA0003 JUY
BB BB0001 LOJ
BB BB0002 JTF
*/
Create table a
(cInvCode Varchar(10)
)Create table b
(Code Varchar(2),
Name Varchar(10))
GO
--插入数据
Insert a Values('AA0001')
Insert a Values('AA0002')
Insert a Values('AA0003')
Insert a Values('BB0001')
Insert a Values('BB0002')Insert b Values('AA','KKD')
Insert b Values('BB','JHG')
GO
Create View [list]
AS
Select B.*,A.* from B Left Join A On B.Code=Left(A.cInvCode,2)
GO
--测试
Select * from list
--删除测试环境
Drop table a,b
Drop View list
--结果
/*
Code cInvCode Name
AA KKD AA0001
AA KKD AA0002
AA KKD AA0003
BB JHG BB0001
BB JHG BB0002
*/