SQL 分析

1. 用一条SQL语句 查询出每门课都大于80分的学生姓名 

name   kecheng    fenshu
张三     语文     81
张三     数学     75
李四     语文     76
李四     数学     90
王五     语文     81
王五     数学     100
王五     英语     90

思路

本题目 已知条件

成绩大于80分，也就是说重这个大集合中分出一个小的集合这个小的集合必须满足一个条件每门课程都大于80分，我们可以重下面入手

1  首先查询出所有大于80分的集合

2  在这个集合中以课程分组

3  另外一个集合查询所有的成绩以课程分组然后统计课程门数

4 只有连个集合的课程门数都相等的情况下才能查询出来

还有一种方法不错比我上面提供的思路要简单

select 姓名 from #成绩 
group by 姓名
having min(分数)>80
首先以姓名分组然后去掉分组里面有分数小于80的集合。

2. 学生表 如下:

自动编号   学号   姓名  课程编号  课程名称  分数
1      2005001 张三  0001   数学    69
2      2005002 李四  0001   数学    89
3      2005001 张三 0001   数学    69
删除除了自动编号不同,其他都相同的学生冗余信息

分析过程

其实这是一个典型的去重问题

解决这种问题的方法之一就是构建两个集合然后在这两个集合中通过关联关系去掉重复的数据

比如这里我们构建两个不通名称学生表通过联合查询此时需要注意的是自动编号不能相同则我们需要 限制编号不相同的方法，例如A.编号 > B.编号

 

3. 一个叫department的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合。

这是一个组合问题这里做一个小小的总结凡是遇到组合问题。都可以构建两个集合然后做交叉查询。

--下面两条语句都可以，多谢wanglinglong提醒
select a.taname,b.taname from #department a,#department b where a.taname < b.taname
select a.taname,b.taname from #department a,#department b where a.taname > b.taname

 

4.怎么把这样一个表
year  month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1992   4     2.4
查成这样一个结果
year  m1  m2  m3  m4
1991  1.1   1.2   1.3   1.4
1992  2.1   2.2   2.3   2.4

思路：这个很明显是一个行列转换

面对这种情况想到两种方法

1   每个单独的列写一个子查询将该行对应的列查询出来

如

select a.years,
(select m.amount from #sales m where months=1 and m.years=a.years) as m1,
(select m.amount from #sales m where months=2 and m.years=a.years) as m2,
(select m.amount from #sales m where months=3 and m.years=a.years) as m3,
(select m.amount from #sales m where months=4 and m.years=a.years) as m4
from #sales a group by a.years

2  用case when语句

select  a.years,
sum(case months when 1 then amount else 0 end) as m1,
sum(case months when 2 then amount else 0 end) as m2,
sum(case months when 3 then amount else 0 end) as m3,
sum(case months when 4 then amount else 0 end) as m4
from #sales a group by a.years

7. 原表:

courseid coursename score

1　　　java 　　       70

2 　　   oracle 　　    90

3 　　   xml 　　         40

4 　　   jsp 　　          30

5 　　   servlet 　　  80

为了便于阅读,查询此表后的结果显式如下(及格分数为60):

分析 此题在最后的增加一列用来判断该列是否过关

这需要我们去判断分数

判断我们常用的就是case when

select course,coursename,
case when score>60 then 'pass' else 'fail' end as mark
from #scores