sql - 如何计算一对多关系

ReporterTbl与AttachmentTbl有一对多关系。

在ReporterTbl中，我有一个ID（101），并且我可以有多个AttachmentTbl与Attachment相关的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，我有多少Attachment个？

表结构：

 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