根据该关系从具有一对多和多对一关系的表中查询数据

Zabeeh Khan 发表于 Dev

扎比·汗（Zabeeh Khan）

尽管我已经使用了很长时间stackoverflow，但这是我第一次发布问题。我希望有人可以帮助我解决这个问题。

我在SQL Server 2005中具有这种数据库结构（请看下图），现在我正在使用Crystal Reports处理报表。

我想做的是从数据库中获取特定实施机构的课程总数（总数），该数据库是特定实施机构（此图中未显示的另一个表，仅与“课程”表有关系）。

我可以使用where语句中的ImplementationingAgencyID列获取课程总数，但是我无法弄清楚如何获取同一机构的主持人和学习者总数（例如，ImplementationingAgencyID 1或2）。

有什么办法吗？数据库中已经有成千上万条记录，因此，即使我必须向所有三个表中添加ImplementingAgencyID列，也无法为旧条目填充此列。它只会添加到新条目中。

谁能帮我解决问题？最好的解决方案是什么？我需要选择查询吗？

---我无法发布图片，因此我将在下面的每个表格中列出重要的列---

Courses (table):
 Id         int
 SerialNum      nvarchar
 ProvinceID     int
 DistrictID     int
 VillageID      int
 EntryUserID        uniqueidentifier
 NearestSchool      nvarchar
 ImplementingAgencyID   int
 FacilitatorID
 CourseVenueID      int
 ...

Learners (table):
 Id
 CourseID
 LearnerName
 ...

Facilitators (table):
 Id
 SerialNum
 FullName
 Age
 ProvinceID
 DistrictID
 VillageID
 ...

Agencies (table):
  Id
  AgencyNameLocal
  AgencyNameEnglish
  ...

关系：1：“课程”表中的“ ImplementingAgencyID”列与“代理”表中的“ Id”列具有多对一关系。

2：“学习者”表中的“ CourseID”列与“课程”表中的“ Id”列具有多对一关系。

3：Courses表中的FacilitatorID列与Facilitators表中的Id列具有多对一关系。

吉迪尔

我希望我能正确理解你。对于每个代理商，您都想知道每种类型的单元（学习者，主持人，课程）有多少个。

该查询应该执行您想做的事情：

;WITH data 
     AS (SELECT T1.*, 
                T2.FULLNAME AS Facil_Name, 
                T3.ID       AS Learner_Name, 
                T4.AGENCYNAME 
         FROM   COURSES T1 
                INNER JOIN FACILITATORS T2 
                        ON T1.FACILITATORID = t2.ID 
                INNER JOIN LEARNERS T3 
                        ON T1.ID = T3.COURSEID 
                INNER JOIN AGENCIES T4 
                        ON T1.IMPLEMENTINGAGENCYID = T4.ID) 

SELECT AGENCYNAME, 
       Count(DISTINCT FACIL_NAME) Per_Agency, 
       'Facil'                    TYPE 
FROM   data 
GROUP  BY AGENCYNAME 
UNION 
SELECT AGENCYNAME, 
       Count(DISTINCT LEARNER_NAME) Per_Agency, 
       'Learner'                    TYPE 
FROM   data 
GROUP  BY AGENCYNAME 
UNION 
SELECT AGENCYNAME, 
       Count(DISTINCT ID) Per_Agency, 
       'Course'           TYPE 
FROM   data 
GROUP  BY AGENCYNAME 
ORDER  BY TYPE