CASE
SQL26 计算25岁以上和以下的用户数量
新知识点：
MySQL 的 case when 的语法有两种：
- 简单函数
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
SELECT NAME '英雄',
CASE NAME WHEN '德莱文'
THEN '斧子' WHEN '德玛西亚-盖伦'
THEN '大宝剑'
WHEN '暗夜猎手-VN'
THEN '弩'
ELSE '无' END '装备'
FROM user_info;
- 搜索函数
CASE WHEN [expr] THEN [result1]…ELSE [default] END
# when 表达式中可以使用 and 连接条件
SELECT NAME '英雄', age '年龄', CASE WHEN age < 18 THEN '少年' WHEN age < 30 THEN '青年' WHEN age >= 30 AND age < 50
THEN '中年' ELSE '老年'
END '状态'
FROM user_info;
题解：
select (case when age >= 25 then '25岁及以上' else '25岁以下' end) 'age_cut', count(device_id) as number from user_profile group by age_cut
day()、month()、year()
SQL28 计算用户8月每天的练题数量
收获：day()，month()，year()可以获取一个date的天，月，年
DISTINCT()、DATEDIFF()
DSQL29 计算用户的平均次日留存率
解题：
SELECT COUNT(distinct q2.device_id,q2.date)/count(DISTINCT q1.device_id,q1.date) as avg_ret from question_practice_detail as q1 left outer join question_practice_detail as q2 on q1.device_id=q2.device_id and DATEDIFF(q2.date,q1.date)=1
收获：
distinct 后面跟多个字段，就需要多个字段同时相同才去重，
datediff(q1.date,q2.date) 返回q1日期与q2日期的天数差值
SUBSTRING()、SUBSTRING_INDEX()
SQL30 统计每种性别的人数
解题：
select substring_index(profile,',',-1) as gender, count(device_id) from user_submit group by gender; SELECT IF(u.profile LIKE '%female','female','male') as gender,COUNT(1) as number FROM user_submit u GROUP BY gender;
收获：
SUBSTRING(str,n,len)
mysql中下标是从1开始的。substring截取字符串。
SELECT SUBSTRING('computer',3) AS col1, -- 从第三个字符到最后 -> SUBSTRING('computer',3,4) AS col2, -- 从第三个字符截取4个 -> SUBSTRING('computer',-3) AS col3, -- 从右到左第三个到最后 -> SUBSTRING('computer',-5,3) AS col4; -- 从右到左第5个截取3个 +--------+------+------+------+ | col1 | col2 | col3 | col4 | +--------+------+------+------+ | mputer | mput | ter | put | +--------+------+------+------+
SUBSTRING_INDEX（str, delim, count）
str：需要分割的串
delim：分隔符
count：当 count 为正数，取第 n 个分隔符之前的所有字符； 当 count 为负数，取倒数第 n 个分隔符之后的所有字符。
