自己的日志記錄
后續(xù)慢慢更新

原帖：數(shù)據(jù)分析SQL面試題目9套匯總
http://www.itdecent.cn/p/0f165dcf9525?utm_campaign=haruki&utm_content=note&utm_medium=reader_share&utm_source=weixin

面試題目二
1.題目如下

image

第二題

select userid, substring_index(GROUP_CONCAT(changjing ORDER BY inttime ),',',2) 
as id from
datafrog_test1
group by userid

用類似的上面的方法。。強(qiáng)行答了一道leetcode上的一道題= =。。。

image.png

image.png

select  na as Deparyment,substring_index(GROUP_CONCAT(name ORDER BY salary ),',',3) as Name from
(select * from employe a join 
(select id as di,name as na  from department)b
on a.department = b.di)p
group by na
order by salary desc

結(jié)果如圖

image.png

這可以說(shuō)是非常尷尬了
慢慢改

第二題

原帖有具體教程和方法和數(shù)據(jù)下載

select 
    t.*,
    concat(次日留存/活躍用戶數(shù)*100,'%')次日留存率,
    concat(三日留存/活躍用戶數(shù)*100,'%')三日留存率,
    concat(七日留存/活躍用戶數(shù)*100,'%')七日留存率
    from
(select
    a.day1,
    count(distinct a.uid) as '活躍用戶數(shù)',
    count(distinct case when day2 - day1 = 1 then a.uid end)次日留存,
    count(distinct case when day2 - day1 = 3 then a.uid end)三日留存,
    count(distinct case when day2 - day1 = 7 then a.uid end)七日留存
from
(select uid,date_format(dayno,'%Y%m%d') as day1 from userinfo where app_name = '相機(jī)')a
join
(select uid,date_format(dayno,'%Y%m%d') as day2 from userinfo where app_name = '相機(jī)')b
on a.uid = b.uid
group by a.day1)t

第三題
列轉(zhuǎn)行

select id,teacher_id,
(case when week_day = 1 then "Yes" else " " end) "mon",
(case when week_day = 2 then "Yes" else " " end) "tue",
(case when week_day = 3 then "Yes" else " " end) "thi",
(case when week_day = 4 then "Yes" else " " end) "thu",
(case when week_day = 5 then "Yes" else " " end) "fri"
 from course;

但也可以嘗試SQL五十練習(xí)第十七題的方法
感覺(jué)可以 失敗了 還沒(méi)想好提煉的規(guī)則，先放著

select 
    a.id,
    a.teacher_id,
    (select id from course where teacher_id = a.teacher_id and id = '01')  as mon,
    (select id from course where teacher_id = a.teacher_id and id = '02')  as tue,
    (select id from course where teacher_id = a.teacher_id and id = '03')  as thi,
    (select id from course where teacher_id = a.teacher_id and id = '04')  as thu,
    (select id from course where teacher_id = a.teacher_id and id = '05')  as fri
from course a

image.png

SQL五十練習(xí)

SQL50練習(xí)-csdn

-- 13、查詢和"01"號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息 
select a.s_id from
(select s_id,group_concat(c_id) as course from score group by s_id)a,
(select s_id,group_concat(c_id) as course from score group by s_id)b
where b.s_id = 01
and a.course = b.course
and a.s_id not in ('01')

第二天再寫一遍，相似的寫法...

-- 13、查詢和"01"號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息 
select b.s_id from
(select s_id,group_concat(c_id) as c1 from score group by s_id)a
,
(select s_id,group_concat(c_id) as c2 from score group by s_id)b
where a.c1 = b.c2 and a.s_id = 01
and b.s_id != 01

做到第十七題覺(jué)得比較有意思，是列轉(zhuǎn)行的題

-- 17、按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)