create table Identitys
(
ID int primary key identity(1,1),
IName nvarchar(50) not null
)
insert Identitys values('歌星')
insert Identitys values('影星')
insert Identitys values('体育明星')
insert Identitys values('模特')
insert Identitys values('赛车手')
insert Identitys values('画家')
create table Star
(
SID int primary key identity(1,1),
SName nvarchar(50) not null,
IDS nvarchar(50) not null
)
insert star values('刘德华','1,2')
insert star values('林志颖','1,2,5')
insert star values('柳岩','4')
insert star values('潘晓婷','3,5')
insert star values('张亮','4')
(
ID int primary key identity(1,1),
IName nvarchar(50) not null
)
insert Identitys values('歌星')
insert Identitys values('影星')
insert Identitys values('体育明星')
insert Identitys values('模特')
insert Identitys values('赛车手')
insert Identitys values('画家')
create table Star
(
SID int primary key identity(1,1),
SName nvarchar(50) not null,
IDS nvarchar(50) not null
)
insert star values('刘德华','1,2')
insert star values('林志颖','1,2,5')
insert star values('柳岩','4')
insert star values('潘晓婷','3,5')
insert star values('张亮','4')
FROM Identitys T1
LEFT JOIN Star T2 ON ','+T2.IDS+','LIKE '%,'+CAST(T1.ID AS VARCHAR(10))+',%'
GROUP BY T1.ID,T1.IName
left join Star on charindex (','+CAST(id as varchar)+',',','+ids+',')>0
group by ID,iname
on CHARINDEX(','+ltrim(a.id)+',',','+b.IDS+',')>0
group by a.id,a.idname order by a.id