Sql练习题总结
1.求公交线
1.数据
1 0 1 四惠
1 0 2 天通苑
1 0 3 立水桥
1 1 1 立水桥
1 1 2 天通苑
1 1 3 四惠
2 0 1 A
2 0 2 B
2 0 3 C
2 1 1 C
2 1 2 B
2 1 3 A
2.见表语句
create table busLog(
line int COMMENT '线路',
direction int COMMENT '方向',
sNumb int COMMENT '站牌号',
name String COMMENT '站名'
) row format delimited fields terminated by '\t';
3.期望结果
line(线路) 上首 上末 下首 下末
1 四惠 立水桥 立水桥 四惠
2 A C C A
4.执行语句
目前想到这样， 有好的思路分享下！！
select
t2.line,
CONCAT_WS('' ,collect_set(t2.ss)),
CONCAT_WS('' ,collect_set(t2.sm)),
CONCAT_WS('' ,collect_set(t2.xs)),
CONCAT_WS('' ,collect_set(t2.xm))
from(
select t1.line,
if(t1.direction==0 and flag2 is null,t1.name,null) as ss,
if(t1.direction==0 and flag1 is null,t1.name,null) as sm,
if(t1.direction==1 and flag2 is null,t1.name,null) as xs,
if(t1.direction==1 and flag1 is null,t1.name,null) as xm from (
select line,
direction,
snumb,
name,
lag(snumb,1) over(partition by line,direction order by snumb desc) as flag1,
lag(snumb,1) over(partition by line,direction order by snumb ) as flag2 from busLog
)t1
)t2
group by t2.line
方法二：
select line,a,b,c,d from (
select line ,FIRST_VALUE(start) over(partition by line) a,FIRST_VALUE(endl) over(partition by line) b,FIRST_VALUE(start2) over(partition by line) c,FIRST_VALUE(end2) over(partition by line) d
from (
select line,start,endl,start2,end2 from (
select line,
FIRST_VALUE(name) over(partition by line,direction order by snumb ) as start,
last_VALUE(name) over(partition by line,direction order by snumb rows between unbounded preceding and unbounded following) as endl,
FIRST_VALUE(name) over(partition by line,direction order by snumb desc) as start2,
last_VALUE(name) over(partition by line,direction order by snumb desc rows between unbounded preceding and unbounded following ) as end2
from busLog
) t1
group by line ,start,endl,start2,end2
) t2
) t3
group by line,a,b,c,d
;
2.时间区间拆分成单独行
-
知识点：
- posexplode: 可以返回爆炸得到的数值，和其数组的索引
- space函数，space(n),返回n个空格
- date_add（date1,int days ） date1加上 days天数
- date_diff (date1,date2) 获得相差的天数
-
建表
create table time_test( id int, start_date date, end_date date) row format delimited fields terminated by '\t'插入数据
insert into time_test(id,start_date,end_date) values(1,'2019-11-1','2019-12-1')想要结果：
查询语句：
select id,start_date,end_date,tf.pos,tf.val,date_add(start_date,pos)
from time_test
lateral view posexplode(split(space(datediff(end_date,start_date)),' ')) tf as pos,val
3.求5分钟内分数增长超过50的学生
创建表：
create table score_test(
id int,
time String,
score double)
row format delimited fields terminated by '\t'
数据：
1 2020-02-02 11:25:31 60
1 2020-02-02 11:26:31 70
1 2020-02-02 11:27:31 80
1 2020-02-02 11:30:31 120
2 2020-02-02 11:25:31 50
2 2020-02-02 11:29:29 120
查询语句
select distinct id
from (
select id,
dt,
hour,
min_stage,
max(score)-min(score) dif_score,
if(max(score)-min(score)>=50,'异常','正常') status
from (
select id,
date_format(time,'yyyy-MM-dd') dt,
hour(time) hour,
floor(minute(time)/5) min_stage,
score
from score_test
) t1
group by id,dt,hour,min_stage
) t2
where t2.dif_score >=50
;
知识点：
五分钟内： 用分钟数 /5 取整，那么时间在五分钟的数据是一样的 （标记为范围）
然后 ，按学生，时间，范围 分组，取出最大成绩和最小成绩 相减
4.关于学生成绩的SQL
- 加载数据
load data local inpath '/export/data/hivedatas/student.txt' into table student;
load data local inpath '/export/data/hivedatas/course.txt' into table course;
load data local inpath '/export/data/hivedatas/teacher.txt' into table teacher;
load data local inpath '/export/data/hivedatas/score.txt' into table score;
-
创建表格：
create table student(s_id string,s_name string,s_birth string,s_sex string) row format delimited fields terminated by '\t';01 赵雷 1990-01-01 男
02 钱电 1990-12-21 男
03 孙风 1990-05-20 男
04 李云 1990-08-06 男
05 周梅 1991-12-01 女
06 吴兰 1992-03-01 女
07 郑竹 1989-07-01 女
08 王菊 1990-01-20 女
create table course(c_id string,c_name string,t_id string) row format delimited fields terminated by '\t';
01 语文 02
02 数学 01
03 英语 03
create table teacher(t_id string,t_name string) row format delimited fields terminated by '\t';
01 张三
02 李四
03 王五
create table score(s_id string,c_id string,s_score int) row format delimited fields terminated by '\t';
01 01 80
01 02 90
01 03 99
02 01 70
02 02 60
02 03 80
03 01 80
03 02 80
03 03 80
04 01 50
04 02 30
04 03 20
05 01 76
05 02 87
06 01 31
06 03 34
07 02 89
07 03 98
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数:
答案：
select s.*,a.s_score as 01_score,b.s_score as 02_score
from student s
join score a
on s.s_id =a.s_id and a.c_id="01"
left join score b
on s.s_id =b.s_id and b.c_id="02"
where a.s_score> b.s_score
– 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数:
select s.*,a.s_score as 01_score,b.s_score as 02_score
from student s
join score a
on s.s_id =a.s_id and a.c_id="01"
left join score b
on s.s_id =b.s_id and b.c_id="02"
where a.s_score < b.s_score
– 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩:
-- 我的答案：
select s.s_id,s.s_name,t1.avg_score
from student s
inner join (
select s_id,avg(s_score) as avg_score
FROM score
group by s_id
having avg_score>=60
) t1
on s.s_id=t1.s_id
-- 资料答案：
select student.s_id,student.s_name,tmp.avg_score from student
join (
select score.s_id,round(avg(score.s_score),1)as avg_score
from score group by s_id)as tmp
on tmp.avg_score>=60
where student.s_id = tmp.s_id
-- 资料答案2：
select student.s_id,student.s_name,round(avg (score.s_score),1) as 平均成绩 from student
join score on student.s_id = score.s_id
group by student.s_id,student.s_name
having avg (score.s_score) >= 60;
– 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩:
– (包括有成绩的和无成绩的)
select s.s_id,s.s_name,t1.avg_score
from student s
inner join (
select s_id,avg(s_score) as avg_score
FROM score
group by s_id
having avg_score<60
) t1
on s.s_id=t1.s_id
union all
select s2.s_id, s2.s_name, 0 as avg_score
from student s2
where s2.s_id not in (select distinct sc2.s_id from score sc2);
– 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩:
-- 我的答案：
select s.s_id,s.s_name,t1.c_idCount,t1.sum_score
from student s
left join
(select s_id,count(c_id) as c_idCount,sum(s_score) as sum_score
FROM score
group by s_id
) t1
-- 资料答案：
select s.s_id,s.s_name,count(sc.c_id) as cc_cid,round(sum(sc.s_score),1) as sum_score
from student s
left join score sc
on s.s_id =sc.s_id
group by s.s_id,s.s_name
– 6、查询"李"姓老师的数量:
select teacher.t_name
from teacher
where teacher.t_name like '李%' group by t_name ;
– 7、查询学过"张三"老师授课的同学的信息:
-- 我的答案：
select s.*
from student s
left join score sc
on s.s_id = sc.s_id
left join course c
on sc.c_id= c.c_id
left join teacher t
on c.t_id = t.t_id
where t.t_name="张三"
-- 资料答案：
select student.* from student
join score on student.s_id =score.s_id
join course on course.c_id=score.c_id
join teacher on course.t_id=teacher.t_id and t_name='张三';
9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息:
并且 可以用inner join
select * from student
inner join (select s_id from score where c_id=01)tmp1
on student.s_id=tmp1.s_id
inner join (select s_id from score where c_id=02)tmp2
on student.s_id=tmp2.s_id
10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息:
select * from student
inner join (select s_id from score where c_id=01)tmp1
on student.s_id=tmp1.s_id
left join (select s_id from score where c_id=02)tmp2
on student.s_id=tmp2.s_id
where tmp2.s_id is null ;
– 11、查询没有学全所有课程的同学的信息:
select * from student s
join (select count(1) as num1 from course) tmp1
left join (select s_id,count(1) as num2 from score
group by score.s_id) t1
on s.s_id = t1.s_id and tmp1.num1 =t1.num2
where t1.s_id is null
udent.s_id=tmp1.s_id
left join (select s_id from score where c_id=02)tmp2
on student.s_id=tmp2.s_id
where tmp2.s_id is null ;
– 11、查询没有学全所有课程的同学的信息:
```sql
select * from student s
join (select count(1) as num1 from course) tmp1
left join (select s_id,count(1) as num2 from score
group by score.s_id) t1
on s.s_id = t1.s_id and tmp1.num1 =t1.num2
where t1.s_id is null
- 12.求一个单词中有多少’l’字符
select 'l', length(charCount.word)-length(regexp_replace(charCount.word,'[l]','')) from charCount;
扫一扫
1482
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
