sql server 2008, COUNT和DISTINCT组合在一起

Question

这是我的代码。总共109行。

1。

SELECT DISTINCT(SELECT top 1 lastname+', '+firstname FROM users WHERE privilege not in 
('Counselor','Guardian') AND userindex IN (SELECT userindex FROM studenttouser 
WHERE studentindex = students.studentindex)) AS teacher

这段代码给了我9行老师的名字。

2。

SELECT COUNT(students.lastname) as total_student
from table
where (SELECT top 1 lastname+', '+firstname FROM users WHERE privilege not in 
('Counselor','Guardian') AND userindex IN (SELECT userindex FROM studenttouser 
WHERE studentindex = students.studentindex))='sam'

这个代码给了我

column1
  34

问题是如何结合在一起。COUNT students.name-column1, DISTINCT teacher-column2

我只需要名单和总数。例如,

name     total_student
sam          24
John         35
Julie        34

等....
我在和数数学生打交道时遇到了麻烦。它本身是可以的，但是当我使用DISTINCT和COUNT时，它会给我一个错误。

I tried

SELECT COUNT(students.lastname) as Student_total, 
DISTINCT(SELECT top 1 lastname+', '+firstname FROM users 
WHERE privilege not in 
('Counselor','Guardian') AND userindex IN (SELECT userindex FROM studenttouser 
WHERE studentindex = students.studentindex)) AS teacher

是行不通的。帮助! !

Answer 1

需要使用Join和Group By。我不能确定，除非知道你的表结构和数据库设计，因为这个表似乎有点复杂，因为它需要一个自连接。你可以试试这几行。

SELECT Name, total_student
FROM
(
SELECT DISTINCT(SELECT top 1 lastname+', '+firstname as Name
FROM users WHERE privilege not in 
   ('Counselor','Guardian') AND userindex IN (SELECT userindex FROM studenttouser 
   WHERE studentindex = students.studentindex))
) teacher
INNER JOIN
(
SELECT COUNT(students.lastname) as total_student
from table 
) as Student 
ON (SELECT top 1 lastname+', '+firstname 
FROM users 
WHERE privilege not in ('Counselor','Guardian') AND userindex IN (SELECT userindex FROM studenttouser 
WHERE studentindex = students.studentindex)
   )=teacher.Name
GROUP BY teacher.Name