sql题解及笔记245-256

245 查找字符串中逗号出现的次数

SELECT id,
    LENGTH(string) - LENGTH(REPLACE(string, ',', '')) AS cnt
    --string长度减去 逗号替换为空字符串的长度 即是逗号数量
FROM strings;

246 获取指定位置的字符串函数RIGHT和LEFT

按照first_name最后两个字母升序进行输出

SELECT first_name FROM employees ORDER BY RIGHT(first_name,2);

right返回从最右边开始指定长度的字符串。LEFT函数就是返回从最左边开始的指定长度字符串

247 聚合函数group_concat（X，Y）

按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接

SELECT dept_no, group_concat(emp_no)AS employees
FROM dept_emp
GROUP BY dept_no;

聚合函数group_concat（X，Y），其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号

248 查找排除最大、最小de平均工资

SELECT (SUM(salary) - MAX(salary) - MIN(salary)) / (COUNT(1)-2) avg_salary 
--COUNT(1) 代表所有数据长度，COUNT(*)
FROM salaries 
where to_date = '9999-01-01';

249 LIMIT 分页查询

每5行一页，返回第2页的数据

SELECT *
FROM employees
LIMIT 5,5

LIMIT 语句结构： LIMIT X,Y 

• Y ：返回几条记录
• X：从第几条记录开始返回（第一条记录序号为0，默认为0）

251 关键字exists查找

select * from employees
where not exists (
    select emp_no
    from dept_emp
    where employees.emp_no=dept_emp.emp_no
)

select * from employees
where emp_no not in(
    select emp_no
    from dept_emp
)

in是先执行子查询，得到一个结果集，将结果集代入外层谓词条件执行主查询，子查询只需要执行一次

exists是先从主查询中取得一条数据，再代入到子查询中，执行一次子查询，判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次

253 CASE WHEN

SELECT e.emp_no, e.first_name, e.last_name, eb.btype, s.salary,
CASE WHEN eb.btype = 1 THEN s.salary * 0.1
WHEN eb.btype = 2 THEN s.salary * 0.2
ELSE s.salary * 0.3
END bonus
FROM emp_bonus AS eb, employees AS e, salaries AS s
WHERE eb.emp_no = e.emp_no
AND s.emp_no = e.emp_no
AND s.to_date='9999-01-01';

基本语法

CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END

不用case分类

select e.emp_no
        ,e.first_name
        ,e.last_name
        ,b.btype
        ,s.salary
        ,(s.salary*b.btype*0.1) as bonus
from employees e
join salaries s on e.emp_no=s.emp_no
join emp_bonus b on e.emp_no=b.emp_no
where s.to_date='9999-01-01';

254 窗口函数 running_total

同一行求累计数 

SELECT emp_no, salary, 
    SUM(salary) OVER(ORDER BY emp_no) AS running_total 
FROM salaries 
WHERE to_date = '9999-01-01'

窗口函数语法

<窗口函数> OVER ([PARTITION BY <列清单>]
            ORDER BY <排序用列清单>)
聚合函数：sum,min,max,avg,count
专用窗口函数：rank,dense_rank,row_number

255 给出奇数行的first_name

从 employees 表中提取 按 first_name 排序后，排名为奇数的 first_name

SELECT e1.first_name
FROM employees e1 --为每个记录分配一个别名 e1，用于后续的比较
WHERE (
    SELECT COUNT(*) --计算排名
    FROM employees e2 
    WHERE e1.first_name >= e2.first_name
    ) % 2 != 0; --选出排名为奇数

256 出现三次及以上的积分

SELECT number FROM grade 
GROUP BY number HAVING COUNT(number)>=3;