sql server 查询多个条件汇总总数 sql语句查询多个值

转载

mob64ca13fb1f2e 2024-02-02 13:51:01

SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。查询多张表的语法是：

SELECT * FROM <表1> <表2>

例如，同时从students表和classes表的“乘积”，即查询数据，可以这么写：

-- FROM students, classes:
SELECT * FROM students, classes;

这种一次查询两个表的数据，查询的结果也是一个二维表，它是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。

这种多表查询又称笛卡尔查询，使用笛卡尔查询时要非常小心，由于结果集是目标表的行数乘积，对两个各自有100行记录的表进行笛卡尔查询将返回1万条记录，对两个各自有1万行记录的表进行笛卡尔查询将返回1亿条记录。

你可能还注意到了，上述查询的结果集有两列id和两列name，两列id是因为其中一列是students表的id，而另一列是classes表的id，但是在结果集中，不好区分。两列name同理

要解决这个问题，我们仍然可以利用投影查询的“设置列的别名”来给两个表各自的id和name列起别名：

-- set alias:

SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM students, classes;

注意，多表查询时，要使用表名.列名这样的方式来引用列和设置别名，这样就避免了结果集的列名重复问题。但是，用表名.列名这种方式列举两个表的所有列实在是很麻烦，所以SQL还允许给表设置一个别名，让我们在投影查询中引用起来稍微简洁一点：

-- set table alias:

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;

注意到FROM子句给表设置别名的语法是FROM <表名1> <别名1>, <表名2> <别名2>。这样我们用别名s和c分别表示students表和classes表。

多表查询也是可以添加WHERE条件的，我们来试试：

-- set where clause:

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;

这个查询的结果集每行记录都满足条件s.gender = 'M'和c.id = 1。添加WHERE条件后结果集的数量大大减少了。

练习

-- 查询student表与class表之间的等值连接

select * from student inner join class
on student.classID=class.classID;

-- 查询student表与grade表之间的等值连接
select * from student inner join grade
on student.studentID=grade.studentID;

-- 查询成绩大于80分学生的学号、姓名、课程号、成绩

select student.studentid as '学号',student.studentID as '姓名',grade.courseid as '课程号',grade.score as '成绩' from student inner join grade
on grade.score>=80;

-- 查询男生的学号、姓名、班级名

select student.studentid as '学号',student.studentname as '姓名',class.className as '班级' from student inner join class
on student.sex='男'
and student.classid=class.classid;

-- 查询学生的学号、姓名、班级名、课程名、成绩
select 
student.studentid as '学号',student.studentname as '姓名',class.className as '班级名',course.coursename as '课程名',grade.score as '成绩'
from student inner join class on student.classid=class.classid
inner join grade on grade.studentID=student.studentID
inner join course on course.courseID=grade.courseID;

-- 查询学生的学号、姓名、课程名、教师名、成绩

select student.studentID,student.studentName,class.classID,teacher.teacherName,grade.score
from student inner join class on student.classID=class.classID
inner join speciality on class.specialityID=speciality.specialityID
inner join department on speciality.departmentID=department.DepartmentID
inner join teacher on department.DepartmentID=teacher.departmentID
inner join grade on student.studentID=grade.studentID;

-- 查询男、女生的平均成绩，显示性别与平均成绩两列

select student.sex,avg(grade.score) as '平均成绩' from student inner join grade
on student.studentID=grade.studentID
group by student.sex;

-- 查询每个班级的平均成绩，显示班级、平均成绩两列

select class.className,avg(grade.score)
from student inner join class on  student.classID=class.classID
inner join grade on student.studentID=grade.studentID
group by class.className;