初入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