SQL练习（2）牛客-数据分析40-49

一、SQL40 分页显示第三页的内容

每页3条结果，页码从1开始
select * from table limit (start-1)*limit,limit; 其中start是页码，limit是每页显示的条数。

select * from user_profile order by device_id limit 6,3

二、SQL41 正则化方法

select id,device_id, university
from user_profile
where (university like '北京%' or university like '上海%' ) and (university like '%职业%' or university like '%专科%' or university like '%成人%' ) and ( university like '%学院' or university like '%校区')

#正则化方法 regexp
select id,device_id, university
from user_profile
where university regexp '^(北京|上海).*(职业|专科|成人).*(学院|校区)$'

常用通配符：. 、* 、 [] 、 ^ 、 $ 、{n}

. : 匹配任意单个字符
* ：匹配0个或多个前一个得到的字符
+ : 匹配前一个字符一次或多次
[] : 匹配任意一个[]内的字符，[ab]*可匹配空串、a、b、或者由任意个a和b组成的字符串。
^ : 匹配开头，如^s匹配以s或者S开头的字符串。
$ : 匹配结尾，如s$匹配以s结尾的字符串。
{n} :匹配前几个字符的n个实例
p1|p2 : 匹配p1或p2模式

三、 SQL43 每个学校有哪些年龄的学生

语法：group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])
注意：函数内部各组成部分之间没有逗号分隔。
group_concat()和concat（）区别

select university,
group_concat(distinct age order by age asc separator ',' ) as age_concat
from user_profile
group by university

四、SQL45-47 子查询

1、先读题，正推和倒推确定每张表查询的内容和表的层次
2、写出每层的表（注意子表需要命名，是否需要distinct）
3、连结各个表

SQL45 每个年龄的学生在各学校最大数量

1、第一层：各个学校每个年龄的学生数量；第二层：每个年龄的最大数量
2、代码：

select u_cnts.age,
max(u_cnt) as max_cnt
from (
    select age,university,count(age) as u_cnt
    from user_profile
    group by age,university
) as u_cnts
#记得写as
group by age
order by max_cnt desc

SQL46 存在绩点大于该校平均绩点时的学生信息

1、第一层：窗口函数，聚合，计算每个学校的平均绩点；第二层：绩点大于该校平均绩点且平均绩点大于3.6
2、代码：

select device_id,
university
from (
    select device_id,
    gpa,
    university,
    avg(gpa)over(partition by university) as avg_gpa
    from user_profile
    group by university,gpa,device_id
)as u2
where gpa > avg_gpa and avg_gpa>3.6
order by device_id desc

SQL47 平均绩点大于3.6且总人数大于2的学校里学生情况

1、方法一：按学校聚合 用if和having写条件
方法二：子查询
（1）第一层：左连接计算平均绩点添加新列
（2）第二层：计算和年龄人数，根据要求选择
2、代码
方法1

select university,
count(distinct device_id) as total_cnt,
sum(if(gender='male',1,0)) as male_cnt,
sum(if(gender='female',1,0)) as female_cnt,
sum(if(age<23,1,0)) as lt23_cnt
from user_profile
group by university
having avg(gpa) > 3.6 and count(distinct device_id) > 2

方法2

SELECT
    c.university,
    COUNT(c.device_id) AS total_cnt,
    SUM(IF(c.gender = 'male', 1, 0)) AS male_cnt,
    SUM(IF(c.gender = 'female', 1, 0)) AS female_cnt,
    SUM(IF(c.age < 23, 1, 0)) AS lt23_cnt
FROM (SELECT
        a.university AS university,
        a.device_id AS device_id,
        a.gender AS gender,
        a.age AS age,
        b.avg_gpa AS avg_gpa
      FROM user_profile AS a
      LEFT JOIN
      (SELECT
         university,
         AVG(gpa) AS avg_gpa
      FROM user_profile
      GROUP BY university) AS b
      ON a.university = b.university) AS c
WHERE c.avg_gpa > 3.6
GROUP BY c.university
HAVING COUNT(c.device_id) > 2;

五、SQL48 强制转换

强制转换函数cast()或者convert()。
语法：
cast(value as type)
convert(value, type)
type类型选择：
binary：二进制类型；
char：字符类型；
date：日期类型；
time：时间类型；
datetime：日期时间类型；
decimal：浮点型；
signed：整型；
unsigned：无符号整型。

select up.device_id,
convert(sum(if(qd.difficult_level='hard',1,0)),signed) as question_cnt
from user_profile as up
left join question_practice_detail as qpd
on up.device_id=qpd.device_id
left join question_detail as qd
on qpd.question_id=qd.question_id
group by device_id
order by question_cnt