SQL数据库表查询，案例备查表

-- 其貌不扬，全是干货

 

1、一个SQL查询出每门课程的成绩都大于80的学生姓名

name   kecheng    fenshu 

张三     语文     81

张三     数学     75

李四     语文     76

李四     数学     90

王五     语文     81

王五     数学     100

王五     英语     90

 SQL1：

　　思路：如果能获得一张表，由学生姓名，语文成绩，数学成绩，英语成绩的表，剩下的就是在WHERE条件中筛选及可以获得想要的结果。

　　具体办法：通过自连接的办法，以“姓名”为连接条件，自连接三次，便可以获得包含又姓名和三门课程成绩的数据行。虽然可以得到想要的数据列。但会有很多冗余重复列！

　　点评：此方法是根据题目，依题解题，中规中矩！ 不过多张表连接非常耗费时间。而且SQL语句也比较复杂，需要注意事项很多。

　　

SELECT D.name FROM ( SELECT S.name,S.score AS ITEM1,S1.score AS ITEM2,S2.score AS ITEM3 FROM Student S inner join Student S1 on S.name = S1.name and S.course <> S1.course inner join Student S2 on S.name = S2.name and S.course <> S2.course WHERE S.score>=80 and S1.score>=80 and S2.score>=80 ) D GROUP BY D.name

　　易错点：内表的 score字段必须要取别名，否则会报错 。

SQL2：

　　思路：采用逆向思维想想。。。。。。求三门成绩都大于80的人，也可以是使先查出有成绩小于80 的人，再除去这些人不就是三门成绩都大于80的人了么？  以前学过的数学逻辑逆向思维还真是有用的阿！！

　　具体办法：先扫描表，查出有成绩小于80的人的姓名，然后再次扫描表，用not in 或not exists 方法。

　　点评：此方法采用逆向思维，能快速写出高效且简单的 SQL语句。

 

//not in SELECT DISTINCT A.name FROM Student A WHERE A.name not in( SELECT Distinct S.name FROM Student S WHERE S.score <80) //not exists SELECT DISTINCT A.name From Student A where not exists (SELECT 1 From Student S Where S.score <80 AND S.name =A.name) /*exists 详解 取出 外表第一条数据 ，然后与内表 根据连接条件 ， 形成一条或多条数据，判断这些生成的数据中是否存在 或者是不存在符合where条件的 。结果为ture的那条外表 记录旧被查询出来！ 实例过程： 取出外表的第一条记录， 和内表通过姓名条件连接，这时候产生2两记录， 根据 not exists是判断不存在。 条件是 score<80 . 而这两条记录存在一条记录小于80，所以于not exists 不符合， 该条记录不被查出。 */

 

SQL3：

SELECT S.name FROM Student S GROUP BY S.name Having MIN(S.score)>=80

 

SQL4:

select name from test.stu

group by name

having count(score) =sum(case when score>80 then 1 else 0 end )

 

SQL5：

select name from stu

group by name

having name not in (

select name from stu

where score <80)

 

SQL6：

select name from

(

select name,MIN(fenshu) FROM cj

GROUP BY name

HAVING MIN(fenshu)>80

) d;

 

 

 2. 查询课程001的成绩大于课程002成绩的学号

 

student表：sno（学号），sname（姓名），sex（性别），dept(系）

course课程表：cno（课程号），课程名（cname）

sc选课表：sno，cno，grade（成绩）

 

select cno from sc a inner join (select * from sc where cno=(select cno from course where cname='001')) as b on a.cno>o=(select cno from course where cname='002')

 

3、关于group by表内容：

 

2005-05-09 胜

2005-05-09 胜

2005-05-09 负

2005-05-09 负

2005-05-10 胜

2005-05-10 负

2005-05-10 负

 

如果要生成下列结果, 该如何写sql语句

 

胜 负

2005-05-09 2 2

2005-05-10 1 2

--------------------------------------------------------

1) select rq,sum(case when shengfu='胜' then 1 else 0 end) as胜,sum(case when shengfu='负' then 1 else 0 end) as负from tab3 group by rq

 

2) select N.rq,N. 胜,M. 负 from

(select rq,count(*) 胜 from tab3 where shengfu='胜'group by rq)N inner join

(select rq,count(*) 负from tab3 where shengfu='负'group by rq)M on N.rq=M.rq

 

3) select a.rq,a. 胜 as胜,b.负 as 负from

(select rq,count(shengfu) 胜from tab3 where shengfu='胜' group by rq) a,

(select rq,count(shengfu) 负from tab3 where shengfu='负' group by rq) b

where a.rq=b.rq;

 

4)select time, sum(decode(status,'胜','')) 胜 ,sum(decode(status,'负','')) 负 from shengfu_table group by time; 　

 

4.表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。

select (case when a>b then a else b end),(case when b>c then b else c end) from tab4

 

5.一个日期判断的sql语句请取出tab5表中日期(SendTime字段)为当天的所有记录 (SendTime字段为datetime型，包含日期与时间)

select * from tab5 t where to_char(t.SendTime,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')

 

6.有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：

大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。

显示格式：

语文 数学 英语

及格 优秀 不及格

-------------------------------------------------------

select

(case when语文>=80 then '优秀' when语文>60 then '及格' else '不及格' end) as 语文,

(case when 数学>=80 then '优秀' when数学>60 then '及格' else '不及格' end) as数学,

(case when英语>=80 then '优秀' when英语>60 then '及格' else '不及格' end) as 英语

from tab5

7.请用一个sql语句得出结果，从table1,table2中取出如table3所列格式数据

table1

月份mon 部门dep 业绩yj

-------------------------------

一月份 01 10

一月份 02 10

一月份 03 5

二月份 02 8

二月份 04 9

三月份 03 8

 

table2

部门dep 部门名称depname

--------------------------------

01 国内业务一部

02 国内业务二部

03 国内业务三部

04 国际业务部

 

table3 （result）

部门dep 一月份 二月份 三月份

---------------------------------------------------

01 10 null null

02 10 8 null

03 5 null 8

04 null 9 null

-------------------------------------------------------

1)

select t.depname,

(select yj from tab6 where mon='一月份' and dep=t.dep) 一月份,

(select yj from tab6 where mon='二月份' and dep=t.dep) 二月份,

(select yj from tab6 where mon='三月份' and dep=t.dep) 三月份

from tab7 t

 

---------------------

2)求总销售额

select

sum(case when t1.mon='一月份' then t1.yj else 0 end) 一月份,

sum(case when t1.mon='二月份' then t1.yj else 0 end) 二月份,

sum(case when t1.mon='三月份' then t1.yj else 0 end) 三月份

from tab7 t,tab6 t1 where t.dep=t1.dep

 

8.一个表中的Id有多个记录，把所有这个id的记录查出来，并显示共有多少条记录数。

-------------------------------------------

select id,count(*) from tab8 group by id having count(*)>1

 

select * from (select tab8,count(id) as num from tab8 group by id) t where t.num>1

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

 

select t.bh||'vs'||t1.bh from tab10 t,tab10 t1 where t.bh$amp;

select t.bh||'vs'||t1.bh from tab10 t,tab10 t1 where t.bh$amp;t1.bh这个是不分的

 

10.怎么把这样一个表

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

 

a):

select t.year,

(select a.amout from tab11 a where a.month=1 and a.year=t.year) m1,

(select b.amout from tab11 b where b.month=2 and b.year=t.year) m2,

(select c.amout from tab11 c where c.month=3 and c.year=t.year) m3,

(select d.amout from tab11 d where d.month=4 and d.year=t.year) m4

from tab11 t group by t.year

 

11.拷贝表(拷贝数据,源表名：a 目标表名：b)

SQL: insert into b(a, b, c) select d,e,f from b;

create table test as select * from dept; --从已知表复制数据和结构

create table test as select * from dept where 1=2; --从已知表复制结构但不包括数据

 

12.显示文章、提交人和最后回复时间

select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

 

13.日程安排提前五分钟提醒

 

 

14.两张关联表，删除主表中已经在副表中没有的信息

delete from fubiao a where a.fid not in(select id from zhubiao)

 

15.有两个表tab12和tab13，均有key和value两个字段，如果tab13的key在tab12中也有，就把tab13的value换为tab12中对应的value

update tab13 set value=(select value from tab12 where tab12.key=tab13.key)

 

16.原表:

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 t.courseid,t.coursename,t.score,(case when score>60 then 'pass' else 'fail' end) mark from tab14 t

 

17.下表

a1 a2

1 a

1 b

2 x

2 y

2 z

用select能选成以下结果吗？

1 ab

2 xyz

SELECT a1, replace(max(sys_connect_by_path(a2, ' ')),' ','') NAME

FROM (SELECT a1, a2, rn, LAG(rn) OVER(PARTITION BY a1 ORDER BY rn) rn1

FROM (SELECT a1, a2, row_number() OVER(ORDER BY a1) rn FROM t) rn)

START WITH rn1 IS NULL

CONNECT BY rn1 = PRIOR rn

GROUP BY a1;

 

18.题为

有两个表, t1, t2,

Table t1:

 

SELLER | NON_SELLER

----- -----

A B

A C

A D

B A

B C

B D

C A

C B

C D

D A

D B

D C

 

Table t2:

 

SELLER | BAL

------ --------

A 100

B 200

C 300

D 400

要求用SELECT 语句列出如下结果:------如A的SUM(BAL)为B,C,D的和,B的SUM(BAL)为A,C,D的和.......

且用的方法不要增加数据库负担,如用临时表等

SELECT SELLER,a.total-t.BAL FROM t,(SELECT SUM(BAL) total FROM t)a;

 

19.删除除了 id 号不同,其他都相同的学生冗余信息

表1，学生表 stu 如下:

id 号 学号 姓名 课程编号 课程名称 分数

1 2005001  张三  0001  数学  69

2 2005002  李四  0001  数学  89

3 2005001  张三  0001  数学   69 

 

第一次写的：

delete from stu where bianhao not in (

select min(bianhao)

from stu as t

group by t.xuehao, t.name, t.kechenghao, t.kecheng, t.fenshu

);

-- 思路：使用group by分组方法，依据重复的几个字段为分组条件分组；并用min()取出每组中编号（id）最小的值，组合重建一个虚表，然后判断原来的表中，不在这个虚表的id就是重复的值。

思路正确！但是写法报错：

 

报错：

按条件删除记录时报You can’t specify target table for update in FROM clause错误

分析原因：

核心概念——mysql中，不能先select一个表的记录，在按此条件进行更新和删除同一个表的记录。解决办法是，将select得到的结果，再通过中间表select一遍，这样就规避了错误，这个问题只出现于mysql，mssql和oracle不会出现此问题。

 

解决办法：

-- 解决思路：将删除、更新操作与原表隔离开。

代码一：

delete from stu1 where bianhao not in (select st1.id from (

select min(bianhao) as id

from stu1 as t

group by t.xuehao, t.name, t.kechenghao, t.kecheng, t.fenshu

) as st1);

 

代码二：

delete from stu1 where bianhao not in (

select min(bianhao) as id

from (select * from stu1) as t

group by t.xuehao, t.name, t.kechenghao, t.kecheng, t.fenshu

);

 

例题2：

 

 

 

 

 

 

 

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

 

select *

from timp a, timp b 

where a.name > b.name

结果：

 

 

=====================================================================================

最后一题：

2 select sid,sum(score)/count(score) from student_course GROUP BY sid having sum(score)/count(score)<80;

 

1 select id,name from student where name in (select name from student group by name having count(*) > 1)order by name,id;

 

3 select id,name from student where id in (select sid from student_course group by sid having min(score)>80);

 

4 select a.name,tab2.Cscore from student as a,(select sid,sum(score) as Cscore from student_course GROUP BY sid) as tab2 where a.id=tab2.sid;

 

5 select a.name,tab2.Cscore from student as a,(select sid,sum(score) as Cscore from student_course GROUP BY sid) as tab2 where a.id=tab2.sid order by tab2.Cscore desc limit 1;

 

 

6 select a.id,a.name,b.score from student as a,(select * from student_course where cid=1 order by score desc) as b where a.id=b.sid and b.score=(select v.s from (select score as s from student_course where cid=1 order by score desc limit 1,1) as v);

 

7 select a.cid,a.sid,a.score from student_course as a,(select cid,max(score) as score from student_course group by cid) c where c.cid=a.cid and c.score=a.score;