Sql练习题总结持续更新

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;