Record of SQL questions of Niuke this week

This topic is mainly about substring_index The use of functions , Pay attention to and substring distinguish .

Complete code ： 

select substring_index(profile,',',-1) gender, count(*) number
from user_submit
group by gender

  The problem is very simple . Observe the law , Use what you learned in the previous question substring Function is OK .

Complete code ：

select device_id,substring(blog_url,11) as user_name
from user_submit

Needless to say .

select substring_index(substring_index(profile,',',3),',',-1)  as age ,count(*) number
from user_submit
group by age
order by age desc

Complete code :

select device_id,university,gpa
from user_profile
where (university,gpa) in(
            select university,min(gpa) from user_profile group by university
           )
order by university

Several points for attention in this problem

1. be used group by keyword , The fields behind it must appear in select Back .

2. be used in keyword , If there are multiple fields , The front and back should be enclosed in parentheses .

Complete code ：

select t1.device_id,t1.university,
        count(t2.question_id) question_cnt,
        sum(case 
            when t2.result ='right' then 1
            else 0
            end
           )right_question_cnt
from user_profile t1 
left join question_practice_detail t2
on(t1.device_id = t2.device_id and month(t2.date)=8)
where(t1.university = ' Fudan University ' )
group by t1.device_id;  

Encounter such a big comprehensive problem ,  Carefully analyze the disassembly requirements .

Correct number of questions , Should be right and wrong assignment , And add the results to get the correct result （ Use of the sum and case function ）

When querying linked tables , Take the time and month requirements into the filter （ It's used here month Time function and linked table query left join ...on...）

By observing the output sample, we can know that it is based on the user id Grouped .