建视图

[qingqingyang]

表ShopShare，结构如下
id(int) ShopID(varchar) OldSkeeper
1 13423,13422,13421,13420 15
2 13418,13417,13416,13415 15
3 13347,13276,13275,13274 18

表shop,结构如下
Sid (int) ShopName(varchar) ShopKeeper
11111 EEEEEE 15
13423 AAAAAA 15
13422 CCCCCC 15

要建一个视图，结构如下（想要得到的数据是shop表中sid 在ShopShare表shopid存在的记录）
ShopID(int) ShopName(varchar) ShopKeeper
13423 AAAAAA 15
13422 CCCCCC 15

[水族杰纶]

select b.*  from shop b inner join shopshare a on charindex(','+ltrim(b.sid)+',',','+ltrim(a.shopid)+',')>0

[水族杰纶]

--表ShopShare，结构如下 
if object_id('shopshare')is not null drop table shopshare
go
create table shopshare(id int, ShopID varchar(30), OldSkeeper int) 
insert shopshare select 1,    '13423,13422,13421,13420',          15 
insert shopshare select 2,    '13418,13417,13416,13415' ,         15 
insert shopshare select 3,    '13347,13276,13275,13274' ,         18 
if object_id('shop')is not null drop table shop
go
create table shop(Sid int ,   ShopName varchar(20),      ShopKeeper int) 
insert shop select 11111,        'EEEEEE' ,                   15 
insert shop select 13423,        'AAAAAA',                    15 
insert shop select 13422,        'CCCCCC'  ,                  15 
select b.*  from shop b inner join shopshare a on charindex(','+ltrim(b.sid)+',',','+ltrim(a.shopid)+',')>0
/*Sid         ShopName             ShopKeeper  
----------- -------------------- ----------- 
13423       AAAAAA               15
13422       CCCCCC               15*/

[rucypli]

create view View_XX
as
select B.sid,B.shopName,B.shopKeeper
from shopshare A,shop B
where charindex(B.sid,A.shopID)>0

[水族杰纶]

--視圖
create view view_name
as
select b.Sid  as ShopID ,   b.ShopName ,      b.ShopKeeper   from shop b inner join shopshare a on charindex(','+ltrim(b.sid)+',',','+ltrim(a.shopid)+',')>0

[lihan6415151528]

charindex函数
第一个参数你要查找的char，第二个参数你被查找的字符串 返回参数一在参数二的位置
select  charindex('a','lihan')
----------- 
4