SQL常用思维总结

1. 将复杂查询拆分为子查询

子查询指的是：一个查询语句嵌套在另一个查询语句内部的查询，作为上级查询的查询条件之一

--查询课程1分数比课程2分数高的学生的学生信息、课程1分数、课程2分数
select student.*,score1,score2 from student join
(select t1.s_id,t1.s_score score1,t2.s_score score2 from
(select * from score where c_id=1) t1 join --子查询1：课程1的分数情况
(select * from score where c_id=2) t2 on t1.s_id=t2.s_id --子查询2：课程2的分数情况
where t1.s_score>t2.s_score) t3 on student.s_id=t3.s_id --子查询3：课程1的分数比课程2的分数高的数据情况

2. group by having条件查询不显示null值，如需显示null需要另加条件

select student.s_id,student.s_name,isnull(AVG(s_score),0) avg_score --使用isnull将聚合函数中的null值显示为0值
from student left join score on student.s_id=score.s_id
group by student.s_id,student.s_name having(AVG(s_score)<60 or AVG(s_score) is null) --需要另加条件以显示null值

3. 聚合函数不显示null值，需要另加isnull

select student.s_id,student.s_name,COUNT(*) coure_num,isnull(SUM(s_score),0) score_sum from --isnull将聚合函数的null值显示为0值
student  left join score on student.s_id=score.s_id
group by student.s_id,student.s_name

4. 内连接（join/inner join）取二者交集，所以内连接可以作为一种条件筛选方法使用

--查询与学号为1的同学所学课程完全相同的学生的信息
select * from student where s_id in
(select sc1.s_id from score sc1 join 
(select * from score where s_id=1) t1 on sc1.c_id=t1.c_id --内连接被用来作为筛选条件：与1学号同学课程相同
where sc1.s_id!=1
group by sc1.s_id having(COUNT(*)=(select COUNT(*) from score where s_id=1))) --group by having被用来进行同值数据计数

5. group by having条件查询前，还可以加where，以此来增加筛选条件

select student.s_id,student.s_name,avg_score from student join
(select s_id,AVG(s_score) avg_score from score where s_score<60 group by s_id having(COUNT(*)>=2)) t1 on student.s_id=t1.s_id
-- group by having 执行的先行条件为s_score<60

6. case when then可以将数据库的行项目拆分成列项目，并进行数值统计

select student.s_id,student.s_name,
SUM(case c_id when 1 then s_score else 0 end) 语文,
SUM(case c_id when 2 then s_score else 0 end) 数学,
SUM(case c_id when 3 then s_score else 0 end) 英语, --将c_id列的行数据拆分为列，并进行数值统计
isnull(AVG(s_score),0) 平均成绩
from student left join score on student.s_id=score.s_id
group by student.s_id,student.s_name
order by 平均成绩 desc

7. case when then可以依据条件进行计数

select course.c_id,course.c_name,
MAX(s_score) 最高分,MIN(s_score) 最低分,AVG(s_score) 平均分,
cast(convert(decimal(18,2),SUM(case when s_score>=60 then 1 else 0 end)/(COUNT(*)+0.0)*100) as varchar)+'%' 及格率,
cast(convert(decimal(18,2),SUM(case when s_score between 70 and 80 then 1 else 0 end)/(COUNT(*)+0.0)*100) as varchar)+'%' 中等率,
cast(convert(decimal(18,2),SUM(case when s_score between 80 and 90 then 1 else 0 end)/(COUNT(*)+0.0)*100) as varchar)+'%' 优良率,
cast(convert(decimal(18,2),SUM(case when s_score>=90 then 1 else 0 end)/(COUNT(*)+0.0)*100) as varchar)+'%' 优秀率 --行数据拆分为列数据，并依据条件进行数值统计
from score join course on score.c_id=course.c_id
group by course.c_id,course.c_name

8. union select可以合并列名相同的查询结果

select * from
(select top 3 course.c_id c_id,course.c_name c_name,student.s_id s_id,student.s_name s_name,score.s_score score from
course join score on course.c_id=score.c_id join student on student.s_id=score.s_id
where course.c_id=1 order by s_score desc) table1
union select * from --数据列相同，数据可以合并查询
(select top 3 course.c_id c_id,course.c_name c_name,student.s_id s_id,student.s_name s_name,score.s_score score from
course join score on course.c_id=score.c_id join student on student.s_id=score.s_id
where course.c_id=2 order by s_score desc) table2
union select * from
(select top 3 course.c_id c_id,course.c_name c_name,student.s_id s_id,student.s_name s_name,score.s_score score from
course join score on course.c_id=score.c_id join student on student.s_id=score.s_id
where course.c_id=3 order by s_score desc) table3

9. 可以利用group by having筛选同值数据

--查询姓名相同且性别相同的学生的姓名，性别以及人数
select s_name,s_sex,COUNT(*) num from student group by s_name,s_sex having(COUNT(*)>1) --group by统计同值数据，having限定同值数据的筛选条件

10. 内连接作为数据筛选条件，group by having统计同值数据记录并执行筛选功能

--查询不同课程分数相同的学生的学号、课程号和成绩
select sc1.s_id,sc1.c_id,sc1.s_score from score sc1 join score sc2 on sc1.s_score=sc2.s_score --内连接作为数据筛选条件：分数相同
group by sc1.s_id,sc1.c_id,sc1.s_score having(COUNT(*)>1) --group by统计同值数据，having限定筛选条件，以实现“不同课程”的查询功能