sql - 连接两个表的问题

Question

我目前正在使用 C# 创建一个应用程序，但我在连接两个表时遇到了一些困难。为了让事情更清楚，这里是我的表结构

表1(员工名单)

| EmployeeID | EmployeeName |
+------------+--------------+
|     1      | John Smith   |
|     2      | Ian Smosh    |

表2(推荐名单)

| PersonalID | InviterID | InterviewerID | 
+------------+-----------+---------------+
|     1      |   1       |       1       | 
|     2      |   1       |       2       | 

Datagridview 的输出应该是

| Employee Name | Invites | Interviews | 
+---------------+---------+------------+
| John Smith    | 2       |      1     | 
| Ian Smosh     | 0       |      1     | 

我目前可以同时获得邀请，但无法同时获得面试。我只能得到一个。

这是我得到的

| Employee Name | Invites | 
+---------------+---------+
|  John Smith   |  2      | 
|  Ian Smosh    |  0      | 

这是我的代码:

SELECT Table1.RecruiterName AS Name, 
    COUNT(Table2.InviterID) AS Invites, 
    COUNT(Table2.InterviewID) AS Interviews 
FROM Table2 LEFT JOIN Table1 ON Table2.InviterID = Table1.EmployeeID 
    AND Table2.InterviewerID = Table1.InviterID 
GROUP BY EmployeeName

有人知道我的代码有什么问题吗？

更新:我设法让它变得更好一点，但我一直在进步

| Employee Name | Invites | Interviews | 
+---------------+---------+------------+
| John Smith    | 2       |      2     | 
| Ian Smosh     | 0       |      1     |

John Smith 的条目只有 2 个邀请和 1 个面试。这是我当前的代码

SELECT Recruiters.RecruiterName AS Name, COUNT(Source.SourceID) AS Source, COUNT(Interview.InterviewID) AS Interview 
FROM Recruiters 
LEFT JOIN Hires Source ON Source.SourceID=Recruiters.RecruiterID 
LEFT JOIN Hires Interview ON Interview.InterviewID=Recruiters.RecruiterID
GROUP BY RecruiterName

为什么 John Smith 在采访中得到的金额是错误的，而 Ian Smosh 是正确的。

Answer 1

double join就是double dipping
这应该可以工作

select employee.EmployeeName, inv.count, int.count 
  from employee 
  join ( select InviterID, 
                count(*) as count 
           from referral 
          group by InviterID     ) as inv 
    on employee.employeeID = inv.InviterID 
  join ( select InterviewerID, 
                count(*) as count 
           from referral 
          group by InterviewerID ) as int 
    on employee.employeeID = int.InterviewerID