有两个表的SQL查询，计数和更多信息

Question

我只是在学这些东西，而且我对这件事有困难。我有两张桌子，STUDENTS和ADVISORS。在“学生”表中使用附加在“顾问”表的主键上的外键为学生分配顾问。

这里的任务是:提供所有顾问的列表和分配给每个人的活跃学生的数量。过滤掉任何有超过一个学生的顾问。

下面列出了当前脚本：

select 
    Students.AdvisorID, count(Students.AdvisorID) as 'TotalStudents'
from 
    Students 
left outer join 
    Advisors on Students.AdvisorID = Advisors.AdvisorID 
where 
    Students.IsActive = 1 
Group by 
    Students.AdvisorID 
Having 
    count(Students.AdvisorID) < 2

这将输出一个正确的列表，只显示advisorID和学生总数。

我还需要显示

Advisors.FirstName + ' ' + Advisors.LastName as 'AdvisorName'

任何帮助都将不胜感激。

编辑

学生桌 顾问桌

Answer 1

我认为您最初的尝试是正确的，但是您需要再次加入到Advisors表中，以便为每个顾问输入姓名。这样做的原因是，在进行聚合之后，剩下的只是每个顾问的ID和学生计数。

SELECT t1.AdvisorID,
       t2.TotalStudents,
       t1.FirstName + ' ' + t1.LastName AS AdvisorName
FROM Advisors t1
INNER JOIN
(
    SELECT a.AdvisorID, COUNT(*) AS TotalStudents
    FROM Advisors a
    LEFT JOIN Students s
        ON a.AdvisorID = s.AdvisorID
    GROUP BY a.AdvisorID
    HAVING COUNT(*) < 2
) t2
    ON t1.AdvisorID = t2.AdvisorID

其他注释：

我选择了给学生做LEFT JOIN顾问，而不是相反，因为你想要每个顾问的统计数据。按照你的第一次加入，它可以过滤掉那些与任何学生不匹配的顾问。这不是你想要的行为，因为一个与任何学生不匹配的顾问应该有一个学生数为零。

Answer 2

下面是一个需要处理的小样本数据

USE tempdb
GO
IF OBJECT_ID('tempdb.dbo.Advisors') IS NOT NULL DROP TABLE dbo.Advisors;
IF OBJECT_ID('tempdb.dbo.Students') IS NOT NULL DROP TABLE dbo.Students;

CREATE TABLE dbo.Advisors (AdvisorID int primary key, AdvisorName varchar(100));
CREATE TABLE dbo.Students 
(
  studentID int identity primary key, 
  AdvisorID int foreign key references dbo.Advisors(AdvisorID)
);

INSERT dbo.Advisors VALUES (1, 'Mr. White'),(2,'Walter Jr.'),(3,'Mr. Pinkman');

INSERT dbo.Students (AdvisorID)
SELECT TOP (20) abs(checksum(newid())%3)+1 FROM sys.all_columns;

没有剩下的加入需要，我认为这会给你的东西，你正在寻找。

SELECT a.AdvisorID, total_students = COUNT(*)
FROM dbo.Advisors a
INNER JOIN dbo.Students s ON a.AdvisorID = s.AdvisorID
GROUP BY a.AdvisorID
HAVING COUNT(*) < 2;