Question

I hava the sql as below:

select a.dept, a.name
  from students a
 group by dept, name
 order by dept, name

结果:

dept   name
-----+---------
CS   | Aarthi
CS   | Hansan
EE   | S.F
EE   | Nikke2

我想总结一下每名优秀学生的金:

dept   name        count
-----+-----------+------  
CS   | Aarthi    |  2
CS   | Hansan    |  2
EE   | S.F       |  2
EE   | Nikke2    |  2
Math | Joel      |  1

我如何写这 s?

Answer 1

虽然看上去看不出所有表格,但我只能假设每个学生的实际入学比例是另一个表。

select a.Dept, count(*) as TotalStudents
  from students a
  group by a.Dept

如果你想要每个与每个学生相关的部门(这并不有意义),你可能不得不这样做。

select a.Dept, a.Name, b.TotalStudents
    from students a,
        ( select Dept, count(*) TotalStudents
             from students
             group by Dept ) b
    where a.Dept = b.Dept

我对你的“Name”一栏的解释是,学生的名字,而不是班级的实际教员的姓名,因此,我的次选/加入。否则,像其他人一样,你们都需要把COUNT(*)作为第三列。

Answer 2

select a.dept, a.name,
       (SELECT count(*)
          FROM students
         WHERE dept = a.dept)
  from students a
 group by dept, name
 order by dept, name

这是一个值得怀疑的问题,因为你拿到本部数字的复制件。挑选学生名单和部门作为单独结果计算比较清洁。当然,可能有一些务实的理由可以采取另一种方式,因此,这并不是绝对规则。

Answer 3

SELECT dept, name, COUNT(name) as CT from students
group by dept, name
order by dept, name

Answer 4

这应当做到(我没有发现任何环境来检测min鱼)。

select a.dept, a.name, count(a.*) as NumOfStudents
from students a
group by dept, name order by dept, name

HTH

Answer 5

否则,仅作书面说明

select dept, name, count(name) as nostud from students group by dept, name order by dept, name

Answer 6

这将产生上文要求的结果。

select a.dept, a.name, cnt
from student a
join (
select dept, count(1) as cnt
from student
group by dept
) b on b.dept = a.dept

友情链接