SELECT * FROM tbiz_ProjectRelation
目标统计每个项目有几条申请记录
Step1
SELECT ProjectID ,
RelationIDs = STUFF(( SELECT ',' + CONVERT(VARCHAR,[RelationID]) FROM tbiz_ProjectRelation t WHERE ProjectID = t1.ProjectID FOR XML PATH('')), 1, 1, '')
FROM tbiz_ProjectRelation t1
Step2
SELECT ProjectID ,
RelationIDs = STUFF(( SELECT ',' + CONVERT(VARCHAR,[RelationID]) FROM tbiz_ProjectRelation t WHERE ProjectID = t1.ProjectID FOR XML PATH('')), 1, 1, '')
FROM tbiz_ProjectRelation t1 GROUP BY ProjectID
Step3
SELECT ProjectID ,
RelationIDs = STUFF(( SELECT ',' + CONVERT(VARCHAR,[RelationID]) FROM tbiz_ProjectRelation t WHERE ProjectID = t1.ProjectID FOR XML PATH('')), 1, 1, ''),
COUNT([RelationID]) AS [COUNT]
FROM tbiz_ProjectRelation t1 GROUP BY ProjectID