求组，包含统计

[yangedie]

两个表
表 Identitys
ID IName
----------- ---------------
1 歌星
2 影星
3 体育明星
4 模特
5 赛车手
6 画家

表 Star，字段IDS对应表Identitys的ID
SID SName IDS
-------- ---------------- ----------
1 刘德华 1,2
2 林志颖 1,2,5
3 柳岩 4
4 潘晓婷 3,5
5 张亮 4

怎么对Identitys做查询统计，以下是错误的
select i.*,count(s.SID) ICount from Identitys i
left join Star s on 1=1
where charindex(','+convert(nvarchar(20),i.ID)+',', ','+IDS+',')>0
group by i.ID,i.IName
order by i.ID

执行结果，少了一条记录（画家）
ID IName ICount
----------- ----------------------- -----------
1 歌星 2
2 影星 2
3 体育明星 1
4 模特 2
5 赛车手 2

[yangedie]

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')

[还在加载中灬]

SELECT T1.ID,T1.IName,COUNT(T2.SID)ICount
FROM Identitys T1
	LEFT JOIN Star T2 ON ','+T2.IDS+','LIKE '%,'+CAST(T1.ID AS VARCHAR(10))+',%'
GROUP BY T1.ID,T1.IName

[frankl123]

select ID,iname,COUNT(Star.SID) from Identitys 
left join Star on  charindex (','+CAST(id as varchar)+',',','+ids+',')>0
group by ID,iname

[yangedie]

还以为on条件只适合字段，2个答案ok

[Ekun_sky]

select a.*,SUM(1) as icount from identitys as a left join star as b
 on CHARINDEX(','+ltrim(a.id)+',',','+b.IDS+',')>0
 group by a.id,a.idname order by a.id