如何在此查询中打印学生姓名？

Question

有关表格如下：

students(rollno, name, deptcode)
depts(deptcode, deptname)
course(crs_rollno, crs_name, marks)

查询是

查找每个系学生的姓名和学籍号码，这些学生在自己的系中获得最高的总分。

考虑：

(一)不同系的课程不同。

( ii)某一系的所有学生都有相同的人数和相同的课程。

那么，只有查询才有意义。

我编写了一个成功的查询，用于显示每个系学生的最高总分。

select do.deptname, max(x.marks) from students so
inner join depts do
on do.deptcode=so.deptcode
inner join(
select s.name as name, d.deptname as deptname, sum(c.marks) as marks from students s
inner join crs_regd c
on s.rollno=c.crs_rollno
inner join depts d
on d.deptcode=s.deptcode
group by s.name,d.deptname) x
on x.name=so.name and x.deptname=do.deptname group by do.deptname;

但如前所述，我也需要显示名字。因此，如果在select列表中包括so.name，则需要将其包含在group by子句中，输出如下：

Kendra Summers       Computer Science                     274
Stewart Robbins      English                              80
Cole Page            Computer Science                     250
Brian Steele         English                              83

预期产出：

Kendra Summers       Computer Science                     274
Brian Steele         English                              83

问题出在哪里？

Answer 1

要用可读的查询解决这个问题，我必须定义几个视图：

total_marks：每个学生的分数之和

create view total_marks as select s.deptcode, s.name, s.rollno, sum(c.marks) as total from course c, students s where s.rollno = c.crs_rollno group by s.rollno;

dept_max：每个系的总分最高的是该系的一个学生。

create view dept_max as select deptcode, max(total) max_total  from total_marks group by deptcode;

这样，我就可以通过查询获得所需的输出。

select a.deptcode, a.rollno, a.name from total_marks a join dept_max b on a.deptcode = b.deptcode and a.total = b.max_total

如果不想使用视图，可以在最终查询中替换它们的选择，这将导致以下结果：

select a.deptcode, a.rollno, a.name 
from 
 (select s.deptcode, s.name, s.rollno, sum(c.marks) as total from course c, students s where s.rollno = c.crs_rollno group by s.rollno) a 
 join (select deptcode, max(total) max_total  from (select s.deptcode, s.name, s.rollno, sum(c.marks) as total from course c, students s where s.rollno = c.crs_rollno group by s.rollno) a_ group by deptcode) b 
    on a.deptcode = b.deptcode and a.total = b.max_total

我相信比我更熟练的人在表现上很容易提高……

如果您(和其他人)想要像我这样尝试它，下面是模式：

create table depts ( deptcode int primary key auto_increment, deptname varchar(20) );
create table students ( rollno int primary key auto_increment, name varchar(20) not null, deptcode int, foreign key (deptcode) references depts(deptcode) );
create table course ( crs_rollno int, crs_name varchar(20), marks int, foreign key (crs_rollno) references students(rollno) );

在这里我插入的所有条目：

insert into depts (deptname) values ("Computer Science"),("Biology"),("Fine Arts");
insert into students (name,deptcode) values ("Turing",1),("Jobs",1),("Tanenbaum",1),("Darwin",2),("Mendel",2),("Bernard",2),("Picasso",3),("Monet",3),("Van Gogh",3);
insert into course (crs_rollno,crs_name,marks) values 
(1,"Algorithms",25),(1,"Database",28),(1,"Programming",29),(1,"Calculus",30),
(2,"Algorithms",24),(2,"Database",22),(2,"Programming",28),(2,"Calculus",19),
(3,"Algorithms",21),(3,"Database",27),(3,"Programming",23),(3,"Calculus",26), 
(4,"Zoology",22),(4,"Botanics",28),(4,"Chemistry",30),(4,"Anatomy",25),(4,"Pharmacology",27),
(5,"Zoology",29),(5,"Botanics",27),(5,"Chemistry",26),(5,"Anatomy",25),(5,"Pharmacology",24),
(6,"Zoology",18),(6,"Botanics",19),(6,"Chemistry",22),(6,"Anatomy",23),(6,"Pharmacology",24),
(7,"Sculpture",26),(7,"History",25),(7,"Painting",30),
(8,"Sculpture",29),(8,"History",24),(8,"Painting",30),
(9,"Sculpture",21),(9,"History",19),(9,"Painting",25) ;

这些插入将加载这些数据：

select * from depts;
+----------+------------------+
| deptcode | deptname         |
+----------+------------------+
|        1 | Computer Science |
|        2 | Biology          |
|        3 | Fine Arts        |
+----------+------------------+
select * from students;
+--------+-----------+----------+
| rollno | name      | deptcode |
+--------+-----------+----------+
|      1 | Turing    |        1 |
|      2 | Jobs      |        1 |
|      3 | Tanenbaum |        1 |
|      4 | Darwin    |        2 |
|      5 | Mendel    |        2 |
|      6 | Bernard   |        2 |
|      7 | Picasso   |        3 |
|      8 | Monet     |        3 |
|      9 | Van Gogh  |        3 |
+--------+-----------+----------+
select * from course;
+------------+--------------+-------+
| crs_rollno | crs_name     | marks |
+------------+--------------+-------+
|          1 | Algorithms   |    25 |
|          1 | Database     |    28 |
|          1 | Programming  |    29 |
|          1 | Calculus     |    30 |
|          2 | Algorithms   |    24 |
|          2 | Database     |    22 |
|          2 | Programming  |    28 |
|          2 | Calculus     |    19 |
|          3 | Algorithms   |    21 |
|          3 | Database     |    27 |
|          3 | Programming  |    23 |
|          3 | Calculus     |    26 |
|          4 | Zoology      |    22 |
|          4 | Botanics     |    28 |
|          4 | Chemistry    |    30 |
|          4 | Anatomy      |    25 |
|          4 | Pharmacology |    27 |
|          5 | Zoology      |    29 |
|          5 | Botanics     |    27 |
|          5 | Chemistry    |    26 |
|          5 | Anatomy      |    25 |
|          5 | Pharmacology |    24 |
|          6 | Zoology      |    18 |
|          6 | Botanics     |    19 |
|          6 | Chemistry    |    22 |
|          6 | Anatomy      |    23 |
|          6 | Pharmacology |    24 |
|          7 | Sculpture    |    26 |
|          7 | History      |    25 |
|          7 | Painting     |    30 |
|          8 | Sculpture    |    29 |
|          8 | History      |    24 |
|          8 | Painting     |    30 |
|          9 | Sculpture    |    21 |
|          9 | History      |    19 |
|          9 | Painting     |    25 |
+------------+--------------+-------+

我借此机会指出，这个数据库设计得很糟糕。这一点在课程表中很明显。基于这些原因：

• 这个名字是单数的
• 此表不代表课程，而是表示考试或分数。
• crs_name应该是引用另一个表的主键的外键(实际上代表课程)。
• 没有限制将分数限制在一个范围内，并且避免学生参加两次相同的考试。
• 我发现将课程与部门联系起来更符合逻辑，而不是学生与部门(这样也会使这些查询更容易)。

我告诉你这是因为我知道你是在从一本书中学习，所以除非书上说“这个数据库设计得很差”，否则不要以这个练习为例来设计你自己的！

无论如何，如果您用我的数据手动解析查询，您将得到以下结果：

+----------+--------+---------+
| deptcode | rollno | name    |
+----------+--------+---------+
|        1 |      1 | Turing  |
|        2 |      6 | Bernard |
|        3 |      8 | Monet   |
+----------+--------+---------+

作为进一步的参考，这里我需要定义的视图的内容：

select * from total_marks;
+----------+-----------+--------+-------+
| deptcode | name      | rollno | total |
+----------+-----------+--------+-------+
|        1 | Turing    |      1 |   112 |
|        1 | Jobs      |      2 |    93 |
|        1 | Tanenbaum |      3 |    97 |
|        2 | Darwin    |      4 |   132 |
|        2 | Mendel    |      5 |   131 |
|        2 | Bernard   |      6 |   136 |
|        3 | Picasso   |      7 |    81 |
|        3 | Monet     |      8 |    83 |
|        3 | Van Gogh  |      9 |    65 |
+----------+-----------+--------+-------+

select *  from dept_max;
+----------+-----------+
| deptcode | max_total |
+----------+-----------+
|        1 |       112 |
|        2 |       136 |
|        3 |        83 |
+----------+-----------+

希望我帮了你！

Answer 2

我想如果你使用窗口功能，这是很容易实现的-

select name, deptname, marks
from (select s.name as name, d.deptname as deptname, sum(c.marks) as marks,
             row_number() over(partition by d.deptname order by sum(c.marks) desc) rn
      from students s
      inner join crs_regd c on s.rollno=c.crs_rollno
      inner join depts d on d.deptcode=s.deptcode
      group by s.name,d.deptname) x
where rn = 1;

Answer 3

尝试以下查询

select a.name, b.deptname,c.marks 
from students a
     , crs_regd b
     , depts c
where a.rollno = b.crs_rollno 
and a.deptcode = c.deptcode 
and(c.deptname,b.marks) in (select do.deptname, max(x.marks) 
                            from students so
                            inner join depts do
                            on do.deptcode=so.deptcode
                           inner join (select s.name as name
                                              , d.deptname as deptname
                                              , sum(c.marks) as marks 
                                      from students s
                                        inner join crs_regd c
                                        on s.rollno=c.crs_rollno
                                        inner join depts d
                                        on d.deptcode=s.deptcode
                                       group by s.name,d.deptname) x
                         on x.name=so.name 
                         and x.deptname=do.deptname 
                         group by do.deptname
     )

内部/子查询将获取课程名称和最大分数，外部查询将获取学生的相应名称。

试着让别人知道你是否得到了想要的结果