526互联

【解惑】介绍三大数据库的with语句的写法及使用场景

发布时间 2023-08-04 08:44:12作者: lanedm

WITH 子句通常被称为 "Common Table Expressions"（CTE），俗称内存临时表，当使用 WITH 语句时，应注意具体的数据库版本和支持情况。以下是对 MySQL、Microsoft SQL Server（MSSQL）和 Oracle 数据库的 WITH 语句用法示例，以及在 WHERE 子句中添加分组关联条件实现对比最大聚合值：

MySQL

在 MySQL5.7 中，可以使用子查询和连接操作来实现 WITH 语句的效果，并结合 GROUP BY 子句实现分组关联条件，但是性能较低，因为每行a.s_price都需要与子查询max(s_price)聚合对比，结果集超过1万行就慢了。以下是示例代码：

SELECT a.col1, a.col2
FROM table1 a
INNER JOIN (
    SELECT item_type, MAX(s_price) AS m_price
    FROM table1
    GROUP BY item_type
) AS max_price ON a.item_type = max_price.item_type
WHERE a.s_price = max_price.m_price;

在MySQL8.0及以上版本，可以使用with语法实现上述功能，且性能较高：

WITH max_price AS (
    SELECT item_type, MAX(s_price) AS m_price
    FROM table1
    GROUP BY item_type
)
SELECT a.col1, a.col2
FROM table1 a
WHERE a.s_price = (SELECT m_price FROM max_price t1 WHERE t1.item_type = a.item_type);

Microsoft SQL Server (MSSQL)

在 MSSQL 中，可以使用 WITH 语句（也称为公共表表达式）来在查询中定义临时的命名结果集，并在 WHERE 子句中添加分组关联条件。以下是示例代码：

WITH max_price AS (
    SELECT item_type, MAX(s_price) AS m_price
    FROM table1
    GROUP BY item_type
)
SELECT a.col1, a.col2
FROM table1 a
WHERE a.s_price = (SELECT m_price FROM max_price t1 WHERE t1.item_type = a.item_type);

此 WITH 语句在 MSSQL 2005 及以上版本中可用。

Oracle

在 Oracle 中，WITH 语句通常被称为子查询块或子查询事务，它使用 WITH 子句为查询定义临时命名的数据块，并在 WHERE 子句中添加分组关联条件。以下是示例代码：

WITH max_price AS (
    SELECT item_type, MAX(s_price) AS m_price
    FROM table1
    GROUP BY item_type
)
SELECT a.col1, a.col2
FROM table1 a
WHERE a.s_price = (SELECT m_price FROM max_price t1 WHERE t1.item_type = a.item_type);

此 WITH 语句在 Oracle 9i 及以上版本中可用。

需要注意的是，这些示例是通用的语法示例，不能适用于所有情况。具体使用和限制还需要参考各个数据库的官方文档或进一步研究各个数据库管理系统的特定语法和功能。