sql语句分析点滴

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

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

这是一个集合分类的题目，面对这样题目我们可以采取两种思维方式

1 正向思维

在该集合中找出所有科目的成绩都大于80分的人的姓名，如果要满足该条件需要做如下两件事情

1  寻找出所有大于80分的课程

2  在所以大于80分的课程中找出课程数量等于考试科目数量的集合

SELECT NAME FROM CHENGJI WHERE FENSHU > 80

GROUP BY NAME HAVING COUNT(KECHENG) = SELECT COUNT(KECHENG) FROM CHENGJI

还有一个更简单的方法使用内置函数

1 先将查找出来的数据以姓名分组

2 将分组好的数据用having 和min函数过滤

SELECT NAME FROM CHENGJI GROUP BY NAME HAVING (MIN(FENSHU)) > 80

2 反向思维

首先在集合中找出含有80分一下的所以科目

然后用一个sql查询不包含第一步中查询出来的集合

select name from chengji where name not in (select name from chengji where fenshu < 80)

这种做饭思路清晰代码也清晰易于分析

2. 学生表 如下:

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

该题目属于去除重复的数据

在取出重复数据中有一个关键字 group by 比如说找出名字相同的数据 则表示为 group by name此时如果名字相同的则被分在一起，这里我们也可以借用这个关键字解决问题

如 delete table1 where  自动编号 not in (select distinct 自动编号 from table1 group by 学号, 姓名,课程编号,课程名称,分数)

在这里也可以用集合的思想来解决这个问题 我们可以将这里处理成两个集合通过关联的方法寻找出这两个集合中相等的数据

delete table where 自动编号 not in (select distinct 自动编号 from table1 a join table1 b on a.自动编号 != b.自动编号 where a.学号 = b.学号 and a.姓名 = b.姓名 and a.课程编号 = b.课程编号 and a.课程名称  = b.课程名称  and a.分数 = b.分数

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

这是一个组合问题，也就是集合的合并问题遇到这中问题我们一般采用 交叉连接查询然后设置两个集合中的关键值不能相等。

 select * from department a  , department b where a.name < b.name

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

 

很明显这是一个行转列的例子面对这种情况我们可以才case when 语句来做判断

如

select year ,

sum(case month when 1 then amount else 0 end ) as m1,

sum(case month when 2 then amount else 0 end) as m2,

sum(case month when 3 then amount else 0 end) as m3,

sum(case month when 4 then amount else 0 end) as m4

from sales group by year

 

7. 原表:

courseid coursename score

1　　　java 　　       70

2 　　   oracle 　　    90

3 　　   xml 　　         40

4 　　   jsp 　　          30

5 　　   servlet 　　  80

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

courseid coursename score mark

1 　　     java 　　      70 　pass

2  　　    oracle 　　  90 　pass

3 　　     xml 　　       40 　fail

4 　　      jsp 　　       30 　fail

5 　　　servlet 　　 80     pass

这是一个增加计算列的类型我们可以对某一个做判断

select courseid , coursename  score,

case when score  > 60 then pass else fail end as mark

from scores

8. 原表: 

id proid proname 
1 1 M 
1 2 F 
2 1 N 
2 2 G 
3 1 B 
3 2 A 
查询后的表: 

id pro1 pro2 
1 M F 
2 N G 
3 B A

select id, 
(select proname from #table1 where proid=1 and id=b.id) as pro1,
(select proname from #table1 where proid=2 and id=b.id) as pro2
from #table1 b group by id

 

1. 已经知道原表
year salary
2000 1000
2001 2000
2002 3000
2003 4000
怎么查询的到下面的结果，就是累积工资
year salary
2000 1000
2001 3000
2002 6000
2003 10000

思路：这个需要两个表交叉查询得到当前年的所有过往年，然后再对过往年进行聚合。代码如下：

select b.years,SUM(a.salary)
from #salary a,#salary b
where a.years<=b.years
group by b.years
order by b.years

请用SQL语句实现：从TestDB数据表中查询出所有月份的发生额都比101科目相应月份的发生额高的科目。请注意：TestDB中有很多科目，都有1－12月份的发生额。
AccID：科目代码，Occmonth：发生额月份，DebitOccur：发生额。
数据库名：JcyAudit，数据集：Select * from TestDB

答：select a.*
from TestDB a 
,(select Occmonth,max(DebitOccur) Debit101ccur from TestDB where AccID='101' group by Occmonth) b
where a.Occmonth=b.Occmonth and a.DebitOccur>b.Debit101ccur

 

面试题：怎么把这样一个表儿
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 

答案一、
select year, 
(select amount from  aaa m where month=1  and m.year=aaa.year) as m1,
(select amount from  aaa m where month=2  and m.year=aaa.year) as m2,
(select amount from  aaa m where month=3  and m.year=aaa.year) as m3,
(select amount from  aaa m where month=4  and m.year=aaa.year) as m4
from aaa  group by year

 

2. 现在我们假设只有一个table，名为pages，有四个字段，id, url,title,body。里面储存了很多网页，网页的url地址，title和网页的内容，然后你用一个sql查询将url匹配的排在最前， title匹配的其次，body匹配最后，没有任何字段匹配的，不返回。

思路：做过模糊搜索对这个应该很熟悉的，可以使用union all依次向一个临时表中添加记录。这里使用order by和charindex来是实现，代码如下：

create table #page(id int, url varchar(100),title varchar(100), body varchar(100))
insert into #page values
(1,null,'abcde','abcde'),
(2,null,'abcde',null),
(3,'abcde','e',null)

select *
from #page 
where url like '%e%' or title like '%e%' or body like '%e%'
order by 
case when (charindex('e', url)>0) then 1 else 0 end desc, 
case when (charindex('e', title)>0) then 1 else 0 end desc, 
case when (charindex('e', body)>0) then 1 else 0 end desc

只要出现一次就会排在前面，这种情况如果两行都出现就会比较下一个字段，以此类推。

还有一种实现，类似于记分牌的思想，如下：

select a.[id],sum(a.mark) as summark  from
(
select #page.*,10 as mark from #page where #page.[url] like '%b%'
union
select #page.*,5 as mark from #page where #page.[title] like '%b%'
union
select #page.*,1 as mark from #page where #page.[body] like '%b%'
) as a  group by id order by summark desc

 

4.有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：  
   大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。  
       显示格式：  
       语文              数学                英语  
       及格              优秀                不及格    
------------------------------------------
select
(case when 语文>=80 then '优秀'
        when 语文>=60 then '及格'
else '不及格') as 语文,
(case when 数学>=80 then '优秀'
        when 数学>=60 then '及格'
else '不及格') as 数学,
(case when 英语>=80 then '优秀'
        when 英语>=60 then '及格'
else '不及格') as 英语,
from table