初入Sql Server 之聚合函数以及多表联合查询

一、简介

在SQL中，聚合函数用于一组数据的处理，所以常常会与SELECT 和GROUP BY 一起使用，比如查询每个学生成绩表中的总分、平均分等等，都需要使用到聚合函数

二、聚合函数的使用

2.1 SUM() 求和函数

假设在表table_score中有字段 col_id,col_student_id,col_datetime,col_score 求出每个col_student_id的总分

SELECT SUN(col_score) '总分' FROM table_score GROUP BY col_student_id

也可求出col_student_id = 2 的总分

SELECT SUN(col_score) '总分' FROM table_score WHERE col_student_id = 2

还可以使用HAVING 过滤掉编号大的数据

SELECT SUN(col_score) '总分' FROM table_score GROUP BY col_student_id HAVING col_student_id >3

2.2 AVG() 求平均值函数

SELECT AVG(col_score) '平均分' FROM table_score GROUP BY col_student_id
SELECT AVG(col_score) '平均分' FROM table_score WHERE col_student_id = 2
SELECT AVG(col_score) '平均分' FROM table_score GROUP BY col_student_id HAVING col_student_id > 3

 2.3 MAX()最大值函数

SELECT MAX(col_score) '最高分' FROM table_score GROUP BY col_student_id
SELECT MAX(col_score) '最高分' FROM table_score WHERE col_student_id = 2
SELECT MAX(col_score) '最高分' FROM table_score GROUP BY col_student_id HAVING col_student_id > 3

2.4 MIN()最小值

SELECT MIN(col_score) '最低分' FROM table_score GROUP BY col_student_id
SELECT MIN(col_score) '最低分' FROM table_score WHERE col_student_id = 2
SELECT MIN(col_score) '最低分' FROM table_score GROUP BY col_student_id HAVING col_student_id > 3

2.5 COUNT()

COUNT() 常常用来求数量，我一般喜欢使用COUNT(*)来计算符合条件的数量，比如求每个学生的考试科目数

SELECT COUNT(*) '数量' FROM table_score GROUP BY col_student_id

 三、联合查询

假设有表table_name1，table_name2 ,表table_name1中的主键tid为表table_name2的外键

1.比较简易的多表连接写法，只能查到两个表中都有的数据，与inner join查询一致

SELECT t1.col1, t1.col2, t2.col1, t2.col2 FROM table_name1 AS t1, table_name2 AS t2 WHERE t1.tid = t2.tid

2.左外联

left outer join 以左边的表为准，只要左边的表中有数据，都会显示出来，若对应右边的表没有数据，就会全部显示为NULL值

SELECT t1.col1, t1.col2, t2.col1, t2.col2 FROM table_name1 AS t1, table_name2 AS t2 LEFT OUTER JOIN t1.tid = t2.tid

3.右外联

right outer join 与左外联相反

SELECT t1.col1, t1.col2, t2.col1, t2.col2 FROM table_name1 AS t1, table_name2 AS t2 RIGHT OUTER JOIN t1.tid = t2.tid

效果类似于下图

 

4.内链接

inner join 查询两边都有的数据

SELECT t1.col1, t1.col2, t2.col1, t2.col2 FROM table_name1 AS t1, table_name2 AS t2 INNER JOIN t1.tid = t2.tid

5.全链接

full outer join  只要一个表中有数据，就都会查询出来

SELECT t1.col1, t1.col2, t2.col1, t2.col2 FROM table_name1 AS t1, table_name2 AS t2 FULL OUTER JOIN t1.tid = t2.tid