ReporterTbl与AttachmentTbl有一对多的关系.
在ReporterTbl中,我有一个ID(101),我可以使用AttachmentTbl多个与ReporterTbl.Id相关的附件
SELECT ISNULL(ReporterTbl.Id,0) AS Id,CONVERT(char(10),ReporterTbl.StartDate,101) AS StartDate,ISNULL(ReporterTbl.PriorityId,0) AS PriorityId,ISNULL(dbo.ReporterTbl.PriorityDesc,'') AS PriorityDesc,(select ReporterTbl.Id,COUNT(dbo.AttachmentTbl.Id) AS attachment_Id FROM dbo.AttachmentTbl RIGHT OUTER JOIN ReporterTbl ON dbo.AttachmentTbl.Id = ReporterTbl.Id GROUP BY ReporterTbl.Id) AS IsAttachment )
基本上,我想知道的是ReporterTbl.ID,我有多少个附件？
表结构：
ReporterTbl
Id int {**PrimaryKey**}
StartDate datetime
PriorityId int
PriorityDesc varchar(500
AttachmentTbl:
AttachmentId indentity
Id {**FK to ReproterTbl**}
Filename
Content
...
解决方法
select r.id,count(a.id) as Count from ReporterTbl r left outer join AttachmentTbl a on r.id = a.id group by r.id
