当前位置： 首页 > news >正文

宜宾建设招标网站创建公司多少钱

news 2025/9/17 2:27:04
宜宾建设招标网站,创建公司多少钱,php是用来做网站的吗,免费网站建设空间mysql with 的用法 (含 with recursive) 相关基础 AS 用法 as 在 mysql 中用来给列/表起别名 如: -- 给列起别名, 把列为name的别名命名为student_name select name as student_name from student; -- 给表起别名, 把表student的别名命名为data_list select * from student…

mysql with 的用法 (含 with recursive)

相关基础

AS 用法

as 在 mysql 中用来给列/表起别名
如:

-- 给列起别名, 把列为name的别名命名为student_name
select name as student_name from student;
-- 给表起别名, 把表student的别名命名为data_list
select * from student as data_list;
-- 给查询结果/表达式起别名
select length(name) as name_length from student;

UNION 用法

union 用于结合多个 sql 查询结果于单个结果集中

query_expression_body UNION [ALL | DISTINCT] query_block[UNION [ALL | DISTINCT] query_expression_body][...]

mysql> SELECT 1, 2;
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
+---+---+mysql> SELECT 'a', 'b';
+---+---+
| a | b |
+---+---+
| a | b |
+---+---+mysql> SELECT 1, 2 UNION SELECT 'a', 'b';
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
| a | b |
+---+---+

with (Common Table Expressions)

Common Table Expressions（CTE）是一个命名的临时结果集，存在于单个语句的范围内，以后该临时结果集可以在该语句中引用, 甚至可能多次引用。
语法:

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

示例:

WITHcte1 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;

--下面两个查询等价
WITH cte (col1, col2) AS
(SELECT 1, 2UNION ALLSELECT 3, 4
)
SELECT col1, col2 FROM cte;WITH cte AS
(SELECT 1 AS col1, 2 AS col2UNION ALLSELECT 3, 4
)
SELECT col1, col2 FROM cte;

用法

  1. 在 select, update, delete 语句前

    WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...

  2. 在子查询前

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

  3. 于含 select 的语句的 select 前

    INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...

  4. 同一级别只允许一个 WITH 子句

    -- 错误示范
WITH cte1 AS (...) WITH cte2 AS (...) SELECT ...
-- 正确示范1
WITH cte1 AS (...), cte2 AS (...) SELECT ...
-- 正确示范2, 语句中可含有多个with, 前提是他们都在不同的层级
WITH cte1 AS (SELECT 1)SELECT * FROM (WITH cte2 AS (SELECT 2) SELECT * FROM cte2 JOIN cte1) AS dt;

  5. 一个 with 语句能定义一个或多个 CTE, 但每个 CTE 在该语句中都是唯一的

    -- 错误示范, 两个cte命名都是cte1
WITH cte1 AS (...), cte1 AS (...) SELECT ...
-- 正确示范
WITH cte1 AS (...), cte2 AS (...) SELECT ...

递归 CTE (Recursive Common Table Expressions)

示例 1 递归增长

WITH RECURSIVE cte (n) AS
(SELECT 1UNION ALLSELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

上述 sql 输出结果如下:

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

该 sql 可以分成两部分, 一部分是非递归部分, 用于初始化行数据:

SELECT 1

另一部分是递归部分:

SELECT n + 1 FROM cte WHERE n < 5

等价于以下代码:

(function test(a) {console.log(a);a++;if (a <= 5) {test(a);}
})(1);// 1
// 2
// 3
// 4
// 5

示例 2 递归字符串拼接

WITH RECURSIVE cte AS
(SELECT 1 AS n, 'abc' AS strUNION ALLSELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

在非严格模式下, 输出以下内容:

+------+------+
| n    | str  |
+------+------+
|    1 | abc  |
|    2 | abc  |
|    3 | abc  |
+------+------+

严格模式下, 则会报错: ERROR 1406 (22001): Data too long for column 'str' at row 1

定义str列时, 用abc定义, 该操作同时定义了长度为length(abc), 故拼接时, 会超出长度
将上述 sql 调整一下

WITH RECURSIVE cte AS
(SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS strUNION ALLSELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

即可正常输出:

+------+--------------+
| n    | str          |
+------+--------------+
|    1 | abc          |
|    2 | abcabc       |
|    3 | abcabcabcabc |
+------+--------------+

限制 CTE 循环次数

输入以下 sql 时, 会提示Recursive query aborted after 1048577 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

WITH RECURSIVE cte (n) AS
(SELECT 1UNION ALLSELECT n + 1 FROM cte
)
SELECT * FROM cte;

默认情况下, cte_max_recursion_depth的值为 1000, 会限制 CTE 的循环次数, 可以通过修改cte_max_recursion_depth修改循环次数上限.

通过修改cte_max_recursion_depth修改循环次数上限后, 可通过limit限制上限.

cte_max_recursion_depth>=limit
故当 limit 较大时, 需先修改cte_max_recursion_depth, 否则较大的limit不生效

WITH RECURSIVE cte (n) AS
(SELECT 1UNION ALLSELECT n + 1 FROM cte LIMIT 10000
)
SELECT * FROM cte;

斐波那契数列

WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(SELECT 1, 0, 1UNION ALLSELECT n + 1, next_fib_n, fib_n + next_fib_nFROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;

+------+-------+------------+
| n    | fib_n | next_fib_n |
+------+-------+------------+
|    1 |     0 |          1 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
+------+-------+------------+

日期序列生成

mysql> SELECT * FROM sales ORDER BY date, price;
+------------+--------+
| date       | price  |
+------------+--------+
| 2022-01-03 | 100.00 |
| 2022-01-03 | 200.00 |
| 2022-01-06 |  50.00 |
| 2022-01-08 |  10.00 |
| 2022-01-08 |  20.00 |
| 2022-01-08 | 150.00 |
| 2022-01-17 |   5.00 |
+------------+--------+

求每日总sales时

mysql> SELECT date, SUM(price) AS sum_priceFROM salesGROUP BY dateORDER BY date;
+------------+-----------+
| date       | sum_price |
+------------+-----------+
| 2022-01-10 |    300.00 |
| 2022-01-13 |     50.00 |
| 2022-01-15 |    180.00 |
| 2022-01-17 |      5.00 |
+------------+-----------+

这样产生的结果, 中间会缺少部分日期.
先写个 sql, 根据日期, 输出中间的日期列表:

WITH RECURSIVE dates (date) AS
(SELECT MIN(date) FROM salesUNION ALLSELECT date + INTERVAL 1 DAY FROM datesWHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT * FROM dates;

+------------+
| date       |
+------------+
| 2022-01-10 |
| 2022-01-11 |
| 2022-01-12 |
| 2022-01-13 |
| 2022-01-14 |
| 2022-01-15 |
| 2022-01-16 |
| 2022-01-17 |
+------------+

结合上述 sql:

WITH RECURSIVE dates (date) AS
(SELECT MIN(date) FROM salesUNION ALLSELECT date + INTERVAL 1 DAY FROM datesWHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT dates.date, COALESCE(SUM(price), 0) AS sum_price
FROM dates LEFT JOIN sales ON dates.date = sales.date
GROUP BY dates.date
ORDER BY dates.date;

+------------+-----------+
| date       | sum_price |
+------------+-----------+
| 2022-01-10 |    300.00 |
| 2022-01-11 |      0.00 |
| 2022-01-12 |      0.00 |
| 2022-01-13 |     50.00 |
| 2022-01-14 |      0.00 |
| 2022-01-15 |    180.00 |
| 2022-01-16 |      0.00 |
| 2022-01-17 |      5.00 |
+------------+-----------+

分层数据遍历

简单写个 sql, 建表并插入数据

CREATE TABLE employees (id         INT PRIMARY KEY NOT NULL,name       VARCHAR(100) NOT NULL,manager_id INT NULL,INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);
INSERT INTO employees VALUES
(117, "Zzs", NULL),      # zzs is the boss (manager_id is NULL)
(198, "John", 117),      # John has ID 198 and reports to 117 (zzs)
(692, "Tarek", 117),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);

此时数据库内数据如下:

mysql> SELECT * FROM employees ORDER BY id;
+------+---------+------------+
| id   | name    | manager_id |
+------+---------+------------+
|   29 | Pedro   |        198 |
|   72 | Pierre  |         29 |
|  117 | Zzs     |       NULL |
|  123 | Adil    |        692 |
|  198 | John    |        117 |
|  692 | Tarek   |        117 |
| 4610 | Sarah   |         29 |
+------+---------+------------+

通过以下 sql, 查询出管理链路:

WITH RECURSIVE employee_paths (id, name, path) AS
(SELECT id, name, CAST(id AS CHAR(200))FROM employeesWHERE manager_id IS NULLUNION ALLSELECT e.id, e.name, CONCAT(ep.path, ',', e.id)FROM employee_paths AS ep JOIN employees AS eON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY path;

查询结果如下:

+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  117 | Zzs     | 117             |
|  198 | John    | 117,198         |
|   29 | Pedro   | 117,198,29      |
| 4610 | Sarah   | 117,198,29,4610 |
|   72 | Pierre  | 117,198,29,72   |
|  692 | Tarek   | 117,692         |
|  123 | Adil    | 117,692,123     |
+------+---------+-----------------+

参考文档

WITH (Common Table Expressions)

http://www.yayakq.cn/news/437834/

相关文章：

  • 认证空间如何显示网站计算机网站建设与管理是什么
  • 全椒网站建设网站悬挂备案号
  • 西丽做网站快速网站
  • 成都有实力的网站建设深圳设计公司深圳市广告设计公司
  • 苏州营销网站设计网站代理被抓
  • wordpress静态网站博客江苏网站建设推广
  • 网站漂浮广告怎么做好公司网站建设
  • jsp网站模版怎样自己做公司网站
  • 河北邢台做网站有偿做设计的网站
  • 手机网站与pc网站同步网站如何做质保系统
  • 哈尔滨建设网站制作在深圳做网站平台需要什么备案
  • 网站收录查询28创业商机网
  • 网站备案人有什么风险网站网络推广
  • 如何做网站建设团队建设移动wap网站
  • 设计素材网站大全网站可以做动态影集的网站
  • 专业网站建设包括哪些母版页和窗体做网站例子
  • 赵县网站建设医疗器械分类
  • 黑龙江学校网站建设黄山seo
  • 做外卖网站的模板哪个网站有帮忙做公开课课件
  • 网站主视觉做网站石材推销
  • 深圳企业网站设wordpress在线支付
  • 网站开发者选项广告视频素材网站
  • 建设银行个人网站官网网站开发w亿玛酷1负责
  • 门户网站集群建设方案ftp中打开wordpress
  • 推荐一个免费网站宁波人才网
  • 电子商务网站建设需要注意什么毕设做网站的系统概述怎么写
  • 贵州三蒲建设工程有限公司网站今天上午北京发生了什么
  • 大连网站建设渠道建设工程安全监督备案网站
  • 个人买卖网站如何做兰州模板型网站建设
  • 网站建设与制开发网站公司名称