SQL备忘录（不定时更新）

concat()，concat_ws()，group_concat()

    concat()：将多个字符串连接成一个字符串，返回结果为连接参数产生的字符串。如果有任何一个参数为null，则返回值为null。

SELECT CONCAT(1,',',2,',',3,',',4) result;

运行结果：1,2,3,4

SELECT CONCAT(1,',',NULL,',',3,',',4) result;

运行结果：NULL

---

    concat_ws()：（concat with separator）和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符。如果分隔符为null，结果全部为null。如果分隔符后面的参数有null，那么会跳过null。

SELECT CONCAT_WS(',',1,2,3,4) result;

运行结果：1,2,3,4

SELECT CONCAT_WS(',',1,NULL,3,4) result;

运行结果：1,3,4

SELECT CONCAT_WS(',',1,2,3,4) result;

运行结果：NULL

---

    group_concat()：group by查询语句中，将同一分组多行数据拼接为一行字符串。

语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )
说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

    使用group_concat()列出每个人的成绩，分隔符为空格

SELECT `name`,GROUP_CONCAT(mark SEPARATOR ' ') result
FROM score1 GROUP BY `name`;

    使用group_concat()以键值对的形式列出每个人的成绩

SELECT `name`,GROUP_CONCAT(`subject`,':',mark ORDER BY `subject`) result
FROM score1 GROUP BY `name`;

    group_concat()注意事项：

1. 用了group_concat后，select 里如果使用了 limit 是不起作用的
2. 用group_concat连接字段的时候是有长度限制的，使用group_concat_max_len系统变量，可以设置允许的最大长度。SET [SESSION | GLOBAL] group_concat_max_len = val;
3. 系统默认的分隔符是逗号

COALESCE()，IFNULL()

    COALESCE() 主要用来进行空值处理，其参数格式： COALESCE ( expression,value1,value2……,valuen)
    COALESCE()函数的第一个参数expression为待检测的表达式，而其后的参数个数不定。
    COALESCE()函数将会返回包括expression在内的所有参数中的第一个非空表达式。
    如果expression不为空值则返回expression；否则判断value1是否是空值，
    如果value1不为空值则返回value1；否则判断value2是否是空值，
    如果value2不为空值则返回value2；
    如果所有的表达式都为空值，则返回NULL。

SELECT COALESCE(1,2,3) result1,
COALESCE(NULL,2,3) result2,
COALESCE(NULL,NULL,3) result3;

运行结果：1，2，3

    COALESCE()函数可以用来完成几乎所有的空值处理，不过在很多数据库系统中都提供了它的简化版，这些简化版中只接受两个变量，例如 IFNULL()

SELECT IFNULL(NULL,666) result;

运行结果：666

SUM()计算多列

SELECT *,SUM( Chinese + Math + English ) sum
FROM score2 GROUP BY `name`

列转行与行转列

    列转行，一列转一行

SELECT `name`,
MAX(IF(`subject`='语文',mark,0)) Chinese,
MAX(IF(`subject`='数学',`mark`,0)) Math,
MAX(IF(`subject`='英语',`mark`,0)) English
FROM score1
GROUP BY `name`;

---

    行转列，一行转一列

SELECT `name`,'语文'  `subject`,Chinese mark FROM score2
UNION
SELECT `name`,'数学'  `subject`,Math mark FROM score2
UNION
SELECT `name`,'英语'  `subject`,English mark FROM score2
ORDER BY `name`;

DISTINCT与GROUP BY转化

    有表如下，id是记录id，user_id是用户id，统计记录数和用户数。

    DISTINCT的做法

SELECT COUNT(id) sum_id,COUNT(DISTINCT user_id) sum_user FROM t1;

    GROUP BY的做法

SELECT sum(sum) sum_id,COUNT(user_id) sum_user FROM (
	SELECT user_id,COUNT(1) sum FROM t1 GROUP BY user_id ) temp;

子查询的名称

    子查询必须设定名称，如果不设置会报错。

关联子查询和exists子查询

SELECT product_type, product_name, sale_price
FROM product t1 WHERE sale_price > (
    SELECT avg(sale_price) FROM product t2
     WHERE t1.product_type = t2.product_type);

    执行逻辑如下：
（1）先执行外层循环，然后取出product _type列的第一个值，进入子查询中，判断where子句条件，如果匹配则计算平均值并返回结果。
（2）重复上述操作，直到所有主查询中的Product表中product _type列记录取完为止。

    查出 article 表中的数据，但要求 uid 必须在 user 表中存在。SQL 语句如下：

SELECT * FROM article WHERE EXISTS (
SELECT * FROM user WHERE article.uid = user.uid);

    将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或 FALSE，所以select可以是任意的）来决定主查询的数据结果是否得以保留。

like匹配特殊字符

    escape可以指定like中使用的转义符是什么，转义符后的字符将被当成原始字符

select * from table where name like '1\_%' escape '\'

select * from table where name like '%\%%' escape '\'

Group By + CASE

    相当于自定义分组

SELECT 
	CASE WHEN mark >= 90 THEN "good"
		 WHEN mark >= 80 THEN 'normal'
		 WHEN mark >= 70 THEN 'pass'
		 ELSE 'bad' END result,
	COUNT(mark) cnt
FROM score1
GROUP BY
	CASE WHEN mark >= 90 THEN "good"
		 WHEN mark >= 80 THEN 'normal'
		 WHEN mark >= 70 THEN 'pass'
		 ELSE 'bad' END

Order By + CASE

SELECT `key` FROM table
 ORDER BY CASE `key`
            WHEN 'B' THEN 1
            WHEN 'A' THEN 2
            WHEN 'D' THEN 3
            WHEN 'C' THEN 4
            ELSE NULL END;

SELECT `key`,
       CASE `key`
         WHEN 'B' THEN 1
         WHEN 'A' THEN 2
         WHEN 'D' THEN 3
         WHEN 'C' THEN 4
         ELSE NULL END AS sort_col
  FROM table
 ORDER BY sort_col;

CASE + 聚合函数

SELECT id,
	CASE WHEN COUNT(*) = 1
		THEN MAX(num)
		ELSE MAX(CASE WHEN flag = 1 THEN uid ELSE NULL END)
	END AS num
FROM table
GROUP BY id;

横向多列最大值和最小值

• 横向最大值：GREATEST(expr_1, expr_2, …expr_n)

SELECT GREATEST(0,4,2,3) max
4
SELECT GREATEST('2020-06-14','2020-04-17','2020-02-09') max
2020-06-14
SELECT GREATEST(0,'7',4,2,3) max
7
SELECT GREATEST(0,4,null,3) max
null
SELECT GREATEST(11) max
Incorrect parameter count in the call to native function 'GREATEST'

• 横向最小值：LEAST(expr_1, expr_2, …expr_n)

SELECT LEAST(0,4,-2,3) min
-2

手动实现DENSE_RANK()和RANK()

    与多表之间进行的普通连接相比，自连接的性能开销更大（特别是与非等值连接结合使用的时候），因此用于自连接的列推荐使用主键或者在相关列上建立索引。

    表数据如下：

name	price
柠檬	30
香蕉	50
橘子	100
香蕉	40
苹果	50
西瓜	80
香蕉	50
香蕉	60

1. 用 RANK() 按 price 由大到小排序

SELECT name, price, RANK() OVER(ORDER BY price) `rank` FROM Products

SELECT a.name, a.price,
	(SELECT COUNT(b.price)
	 FROM Products b
	 WHERE a.price < b.price) + 1 AS `rank`
FROM Products a
ORDER BY `rank`;

    子查询的意思是返回比当前价格大的商品数量，如100是最大的，没有比它大的，所以为0。

价格	比自己高的价格	比自己高的价格的个数（这就是位次）
100	-	0
80	100	1
50	100, 80	2
30	100, 80, 50	3

运行结果：

name	price	rank
橘子	100	1
西瓜	80	2
香蕉	60	3
苹果	50	4
香蕉	50	4
香蕉	50	4
香蕉	40	7
柠檬	30	8

    自连接做法：

SELECT a.name,
       MAX(a.price) AS price,
       COUNT(b.name) +1 AS `rank`
  FROM Products a LEFT OUTER JOIN Products b
    ON a.price < b.price
 GROUP BY a.name
 ORDER BY `rank`;

2. 用 RANK() 按 price 由小到大排序

SELECT a.name, a.price,
	(SELECT COUNT(b.price)
	 FROM Products b
	 WHERE a.price > b.price) + 1 AS `rank`
FROM Products a
ORDER BY `rank`;

3. 用 RANK() 按 price 由大到小分组排序

SELECT name, price, RANK() OVER(PARTITION BY name ORDER BY price) `rank` FROM Products

SELECT a.name, a.price,
	(SELECT COUNT(b.price)
	 FROM Products b
	 WHERE a.price < b.price AND a.name = b.name) + 1 AS `rank`
FROM Products a
ORDER BY a.name,`rank`;

运行结果：

name	price	rank
柠檬	30	1
橘子	100	1
苹果	50	1
西瓜	80	1
香蕉	60	1
香蕉	50	2
香蕉	50	2
香蕉	40	4

4. 用 DENSE_RANK() 按 price 由小到大排序（加 DISTINCT）

SELECT name, price, DENSE_RANK() OVER(ORDER BY price) `rank` FROM Products

SELECT a.name, a.price,
	(SELECT COUNT(DISTINCT b.price)
	 FROM Products b
	 WHERE a.price < b.price) + 1 AS `rank`
FROM Products a
ORDER BY `rank`;

运行结果：

name	price	rank
橘子	100	1
西瓜	80	2
香蕉	60	3
苹果	50	4
香蕉	50	4
香蕉	50	4
香蕉	40	5
柠檬	30	6

---

SELECT a.district,a.name,
	   max(a.price) price,
	   count(b.name) + 1 rnk
FROM DistrictProducts a
	LEFT JOIN DistrictProducts b
	ON a.district = b.district AND a.price < b.price
GROUP BY a.district,a.name
ORDER BY a.district,rnk