oracle
with 创建临时表
在 Oracle 数据库中,WITH
语句也被称为子查询事实表或公共表表达式 (CTE)。它可以用来创建一个只在当前查询中有效的临时表。你可以在同一个查询中多次引用这个临时表,这对于编写复杂的 SQL 查询非常有 WITH
语句的基本用法如下:
WITH temp_table AS (
SELECT column1, column2, ...
FROM table_name
WHERE conditions
)
SELECT column1, column2, ...
FROM temp_table
WHERE conditions;
在这个例子中,temp_table
就是我们创建的临时表,它包含从 table_name
中选取的数据。我们可以在后续的查询中,像使用普通数据库表一样使用这个临时表。
如果你需要在一个查询中创建多个临时表,可以使用逗号将它们分隔开,如下所示:
WITH temp_table1 AS (
SELECT column1, column2, ...
FROM table_name1
WHERE conditions
), temp_table2 AS (
SELECT column1, column2, ...
FROM table_name2
WHERE conditions
)
SELECT column1, column2, ...
FROM temp_table1
JOIN temp_table2 ON conditions;
在这个例子中,我们创建了两个临时表 temp_table1
和 temp_table2
,然后在最后的查询中将它们进行联接。
字符串拼接函数
Oracle WM_CONCAT () 函数
Oracle WM_CONCAT () 函数用于将多个字符串值连接成单个字符串值。该函数的语法如下:
WM_CONCAT(string_expression)
其中:
- string_expression:要连接的字符串表达式。可以是列名、常量或表达式。
用法
WM_CONCAT () 函数可以用于将多个字符串值连接成单个字符串值,并将其显示在查询结果中。例如,以下查询将连接表 customers
中的 first_name
和 last_name
列,并将结果显示在 full_name
列中:
SELECT customer_id, WM_CONCAT(first_name || ' ' || last_name) AS full_name
FROM customers;
结果如下:
customer_id | full_name
------------+------------------------
1 | John Smith
2 | Mary Johnson
3 | Robert Jones
WM_CONCAT () 函数还可以用于将多个字符串值连接成单个字符串值,并将其存储在表中。例如,以下查询将连接表 customers
中的 first_name
和 last_name
列,并将结果存储在 full_name
列中:
UPDATE customers
SET full_name = WM_CONCAT(first_name || ' ' || last_name);
注意
- WM_CONCAT () 函数返回的字符串值的最大长度为 4000 字节。
- WM_CONCAT () 函数可以连接多个字符串值,但每个字符串值的最大长度不能超过 4000 字节。
- WM_CONCAT () 函数可以连接空字符串值,但空字符串值不会影响连接结果。
- WM_CONCAT () 函数可以连接重复的字符串值,但重复的字符串值只会显示一次。
示例
SELECT WM_CONCAT(DISTINCT department_name)
FROM departments;
-- 结果:
--
-- Sales
-- Marketing
-- Finance
-- Human Resources
SELECT WM_CONCAT(DISTINCT department_name, ', ')
FROM departments;
-- 结果:
--
-- Sales, Marketing, Finance, Human Resources
SELECT WM_CONCAT(DISTINCT department_name || ' (' || department_id || ')')
FROM departments;
-- 结果:
--
-- Sales (10), Marketing (20), Finance (30), Human Resources (40)
START WITH … CONNECT BY 的用法
START WITH
子句用于指定递归查询的开始点。它与 CONNECT BY
子句一起使用,用于在层次数据上执行递归查询。
START WITH
子句的语法如下:
START WITH <condition>
其中,<condition>
是一个条件表达式,用于指定递归查询的开始点。
例如,以下查询使用 START WITH
子句来查找所有从员工 1 开始的员工:
SELECT employee_id, manager_id, employee_name
FROM employees
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id;
上面的查询将返回以下结果:
+------------+------------+--------------+
| employee_id | manager_id | employee_name |
+------------+------------+--------------+
| 1 | NULL | John Smith |
| 2 | 1 | Jane Doe |
| 3 | 2 | Michael Jones |
| 4 | 3 | Mary Johnson |
+------------+------------+--------------+
START WITH
子句还可以与其他条件一起使用。例如,以下查询使用 START WITH
子句来查找所有从员工 1 开始且工资高于 10000 美元的员工:
SELECT employee_id, manager_id, employee_name
FROM employees
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id
AND salary > 10000;
上面的查询将返回以下结果:
+------------+------------+--------------+
| employee_id | manager_id | employee_name |
+------------+------------+--------------+
| 1 | NULL | John Smith |
| 2 | 1 | Jane Doe |
| 3 | 2 | Michael Jones |
+------------+------------+--------------+
START WITH
子句对于在层次数据上执行递归查询非常有用。它可以用于查找所有从给定节点开始的节点、查找给定节点的所有祖先节点或查找给定节点的所有子孙节点。
评论区