CASE function
Syntax
1
2
3
4
5
6
|
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
|
COALESCE function
Definition and Usage
The COALESCE() function returns the first non-null value in a list.
Syntax
COALESCE(val1, val2, …., val_n)
WITH (Common Table Expressions)
公用表表达式 (CTE) 是一个命名的临时结果集，它存在于单个语句的范围内，稍后可以在该语句中引用，可能会多次引用。
对于逻辑复杂的sql，with可以大大减少临时表的数量，提升代码的可读性、可维护性
Common Table Expressions
1
2
3
4
5
|
WITH
cte1 AS (SELECT a, b FROM table1),
cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;
|
Recursive Common Table Expressions
A recursive common table expression is one having a subquery that refers to its own name. For example:
1
2
3
4
5
6
7
|
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;
|
递归公用表表达式 (CTE) 经常用于序列生成和遍历分层或树结构数据。
递归CTE示例
斐波那契数列生成
1
2
3
4
5
6
7
8
|
WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
SELECT 1, 0, 1
UNION ALL
SELECT n + 1, next_fib_n, fib_n + next_fib_n
FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;
|
连续日期
1
2
3
4
5
6
7
8
|
WITH RECURSIVE dates (date) AS
(
SELECT MIN(date) FROM sales
UNION ALL
SELECT date + INTERVAL 1 DAY FROM dates
WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT * FROM dates;
|
分层数据遍历
1
2
3
4
5
6
7
8
9
10
11
|
WITH RECURSIVE employee_paths (id, name, path) AS
(
SELECT id, name, CAST(id AS CHAR(200))
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
FROM employee_paths AS ep JOIN employees AS e
ON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY path;
|
行列转换
| name |
course |
grade |
| zhangsan |
语文 |
80 |
| zhangsan |
数学 |
90 |
|
|
|
| name |
语文 |
数学 |
| zhangsan |
80 |
90 |
|
|
|
1
2
3
4
|
select name,
(case when course = '语文' then grade else 0 end) as 语文,
(case when course = '数学' then grade else 0 end) as 数学
from test group by name;
|
如果记录中需要求和可以 sum(case ... end) as xxx
1
2
3
4
|
select name,
if(course = '语文', grade, 0) as 语文,
if(course = '数学', grade, 0) as 数学
from test group by name;
|