XiaoLin's Blog

Xiao Lin

数据库开发时使用到的技巧记录

15
2024-01-30

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_table1temp_table2,然后在最后的查询中将它们进行联接。

字符串拼接函数

Oracle WM_CONCAT () 函数

Oracle WM_CONCAT () 函数用于将多个字符串值连接成单个字符串值。该函数的语法如下:

WM_CONCAT(string_expression)

其中:

  • string_expression:要连接的字符串表达式。可以是列名、常量或表达式。

用法

WM_CONCAT () 函数可以用于将多个字符串值连接成单个字符串值,并将其显示在查询结果中。例如,以下查询将连接表 customers 中的 first_namelast_name 列,并将结果显示在 full_name 列中:

SELECT customer_id, WM_CONCAT(first_name || ' ' || last_name) AS full_name
FROM customers;

结果如下:

image.png

customer_id | full_name
------------+------------------------
1           | John Smith
2           | Mary Johnson
3           | Robert Jones

WM_CONCAT () 函数还可以用于将多个字符串值连接成单个字符串值,并将其存储在表中。例如,以下查询将连接表 customers 中的 first_namelast_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 子句对于在层次数据上执行递归查询非常有用。它可以用于查找所有从给定节点开始的节点、查找给定节点的所有祖先节点或查找给定节点的所有子孙节点。