查询某一批名字的人mysql_MYSQL之——查询练习题

1、用一条SQL 语句 查询出`chengji`表，每门课都大于80 分的学生姓名。

name   kecheng   fenshu

张三     语文         81

张三     数学         75

李四     语文         76

李四     数学         90

王五     语文         81

王五     数学         100

王五     英语         90

sql：select distinct name from chengji where name not in (select name from chengji where fenshu<=80 );

sql：select name from chengji group by name having min(fenshu)>80

思路：筛选出小于等于80的分数的名字，过滤掉小于等于80的分数的名字，再对名字进行去重

对名字进行分组，再判断大于80的分数

注：分组前，条件使用where；分组后，条件使用having；having后通常跟聚合函数

2、学生表 如下:

自动编号　　　　学号   　　　　姓名　　　　课程编号　　　　课程名称　　　　分数

1        　　　　　2005001　　　张三 　　　　0001      　　　　数学　　　　　　69

2        　　　　　2005002　　　李四　　　　 0001      　　　　数学　　　　　　89

3        　　　　　2005001　　　张三　　　　 0001      　　　　数学　　　　　　69

1)查询出重复的数据，且id为最大或者最小的

sql：select max(id) id,name,kecheng,fenshu from chengji group by fenshu,kecheng,name having count(name)>1

sql：select min(id) id,name,kecheng,fenshu fromchengji group by fenshu,kecheng,name having count(name)>1

2)删除除了自动编号不同, 其他都相同的学生冗余信息。

sql：

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

思路：先把team分成两个表；两表组合；去掉重复的组合

sql：select a.name,b.name from team a,team b where a.name

4、

问题：

1)查询张三的2019年生产产品的销售量(使用left join 关键字)

sql：

select

*

from sales

left join deal on sales.S=deal.S

left join production on deal.P=production.P

where Sname='张三' and Pdate like '2019%';

2)查询生产日期2020年的一季度各产品的总销量

sql：

select

t2.P,t2.Pname, SUM(Voulume),t2.Pdate

from deal t1

left join production t2 on t1.P=t2.P

where t2.Pdate like '2020%'

GROUP BY t2.P;

3)查询销售量最高的产品的销量(使用group by关键字)

sql：

select

a.P,a.Pname,max(a.sum1)

from

(

select

t1.P,t1.Pname,sum(Voulume) as sum1

from

production t1

left join deal t2 on t1.P=t2.P

group by t1.P

) a;

5、表名：test_tb_grade

图1

图2

将图1转换为图2

sql：

SELECT id,user_name ,

MAX(CASE course WHEN '数学' THEN score ELSE 0 END ) 数学,

MAX(CASE course WHEN '语文' THEN score ELSE 0 END ) 语文,

MAX(CASE course WHEN '英语' THEN score ELSE 0 END ) 英语

FROM test_tb_grade

GROUP BY USER_NAME;

6、表名：ta

图1

图2

对图1表查成图2的结果

sql：select date_format(datas,'%Y-%m-%d') '日期' from ta

7、查询出近一周/一月/一年的数据

select * from ta where DATEDIFF(now(),datas)<=7;

select * from ta where DATEDIFF(now(),datas)<=30;

select * from ta where DATEDIFF(now(),datas)<=365;

8、请写出一个SQL来查询分数前五5的所有人？

select * from 分数表 order by 分数 desc limit 5;