[大数据]MySQL with、union all语句

表：

WITH t1 AS (
	SELECT
		'2022-02-10' AS date,
		gamename,
		count( id ) AS num 
	FROM
		user_search_log 
	WHERE
		created_at BETWEEN '2022-02-10 00:00:00' 
		AND '2022-02-10-23:59:59' 
		AND gamename IS NOT NULL 
	GROUP BY
		gamename 
	ORDER BY
		num DESC 
		LIMIT 5 
	),
	t2 AS (
	SELECT
		'2022-02-11' AS date,
		gamename,
		count( id ) AS num 
	FROM
		user_search_log 
	WHERE
		created_at BETWEEN '2022-02-11 00:00:00' 
		AND '2022-02-11-23:59:59' 
		AND gamename IS NOT NULL 
	GROUP BY
		gamename 
	ORDER BY
		num DESC 
		LIMIT 5 
	),
	t3 AS (
	SELECT
		'2022-02-14' AS date,
		gamename,
		count( id ) AS num 
	FROM
		user_search_log 
	WHERE
		created_at BETWEEN '2022-02-14 00:00:00' 
		AND '2022-02-14-23:59:59' 
		AND gamename IS NOT NULL 
	GROUP BY
		gamename 
	ORDER BY
		num DESC 
		LIMIT 5 
	) SELECT
	* 
FROM
	t1 UNION ALL
SELECT
	* 
FROM
	t2 UNION ALL
SELECT
	* 
FROM
	t3
ORDER BY
	date desc

若无最后的排序或分页语句，则上面SQL可简化

( SELECT
	'2022-02-10' AS date,
	gamename,
	count( id ) AS num 
	FROM
		user_search_log 
	WHERE
		created_at BETWEEN '2022-02-10 00:00:00' 
		AND '2022-02-10-23:59:59' 
		AND gamename IS NOT NULL 
	GROUP BY
		gamename 
	ORDER BY
		num DESC 
		LIMIT 5 
	) UNION ALL
	(
	SELECT
		'2022-02-11' AS date,
		gamename,
		count( id ) AS num 
	FROM
		user_search_log 
	WHERE
		created_at BETWEEN '2022-02-11 00:00:00' 
		AND '2022-02-11-23:59:59' 
		AND gamename IS NOT NULL 
	GROUP BY
		gamename 
	ORDER BY
		num DESC 
		LIMIT 5 
	) UNION ALL
	(
	SELECT
		'2022-02-14' AS date,
		gamename,
		count( id ) AS num 
	FROM
		user_search_log 
	WHERE
		created_at BETWEEN '2022-02-14 00:00:00' 
		AND '2022-02-14-23:59:59' 
		AND gamename IS NOT NULL 
	GROUP BY
		gamename 
	ORDER BY
		num DESC 
		LIMIT 5 
	) 

注意：WITH语句仅MySQL版本8.0以上才支持

AS 用法:
AS在mysql用来给列/表起别名.
有时，列的名称是一些表达式，使查询的输出很难理解。要给列一个描述性名称，可以使用列别名。

要给列添加别名，可以使用AS关键词后跟别名
例子1:

SELECT 
 [column_1 | expression] AS col_name
FROM table_name;

如果别名包含空格，则必须引用以下内容：
例子2:

SELECT 
 [column_1 | expression] AS 'col name'
FROM table_name;

with(Common Table Expressions/CTE)用法:
with在mysql中被称为公共表达式,可以作为一个临时表然后在其他结构中调用.如果是自身调用那么就是后面讲的递归.
语法:

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

cte_name :公共表达式的名称,可以理解为表名,用来表示as后面跟着的子查询
col_name :公共表达式包含的列名,可以写也可以不写

例子1:

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;

例子2:

WITH cte (col1, col2) AS
(
  SELECT 1, 2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;

例子3:
这里的第一个as后面接的是子查询,第二个as表示列名,而不是子查询

WITH cte AS
(
  SELECT 1 AS col1, 2 AS col2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;

with的合法用法:
在子查询(包括派生的表子查询)的开始处

SELECT ... WHERE id IN (WITH ... SELECT ...) ...
SELECT * FROM (WITH ... SELECT ...) AS dt ...

同一级别只允许一个WITH子句。同一级别的WITH后面跟着WITH是不允许的,下面是错误用法:

#错误用法，同级不允许多个WITH
WITH cte1 AS (...) WITH cte2 AS (...) SELECT ...

应改为:

WITH cte1 AS (SELECT 1)
SELECT * FROM (
	WITH cte2 AS (SELECT 2) 
	SELECT * FROM cte2 JOIN cte1
) AS dt;

在这里面as代表列名,sql不是顺序执行的,这一点了解的话就很好理解这个as了