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

Mysql复杂的查询语句有哪些

article 2024/11/17 1:46:02

MySQL 支持多种复杂的查询语句，用于处理复杂的数据检索、分析和操作需求。以下是一些常见的复杂查询语句示例，包括联接、子查询、联合、分组、窗口函数等，帮助你理解如何在 MySQL 中执行复杂的数据操作。

1. 联接（JOIN）

联接用于从多个表中检索数据。常见的联接类型包括内联接（INNER JOIN）、左联接（LEFT JOIN）、右联接（RIGHT JOIN）和全外联接（FULL JOIN）。

1.1 内联接

内联接返回两个表中匹配的行。

SELECT
    orders.order_id,
    customers.customer_name
FROM
    orders
INNER JOIN
    customers
ON
    orders.customer_id = customers.customer_id;

1.2 左联接

左联接返回左表中的所有行，以及右表中匹配的行。如果右表中没有匹配的行，则结果中包含 NULL。

SELECT
    orders.order_id,
    customers.customer_name
FROM
    orders
LEFT JOIN
    customers
ON
    orders.customer_id = customers.customer_id;

1.3 右联接

右联接返回右表中的所有行，以及左表中匹配的行。如果左表中没有匹配的行，则结果中包含 NULL。

SELECT
    orders.order_id,
    customers.customer_name
FROM
    orders
RIGHT JOIN
    customers
ON
    orders.customer_id = customers.customer_id;

2. 子查询（Subquery）

子查询是嵌套在其他查询中的查询，可以在 SELECT、WHERE、HAVING 子句中使用。

2.1 标量子查询

返回单一值的子查询。

SELECT
    order_id,
    order_date
FROM
    orders
WHERE
    customer_id = (SELECT customer_id FROM customers WHERE customer_name = 'John Doe');

2.2 相关子查询

依赖于外部查询的子查询。

SELECT
    order_id,
    order_date
FROM
    orders o
WHERE
    EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id AND c.customer_name = 'John Doe');

3. 联合（UNION）

联合用于将两个或多个查询的结果组合在一起。默认情况下，UNION 去除重复的记录，使用 UNION ALL 可以包含重复记录。

SELECT
    customer_id,
    customer_name
FROM
    customers
WHERE
    customer_status = 'Active'

UNION

SELECT
    customer_id,
    customer_name
FROM
    archived_customers
WHERE
    customer_status = 'Active';

4. 分组和聚合函数

分组用于将数据按特定列分组，并可以对每组数据应用聚合函数（如 COUNT、SUM、AVG、MAX、MIN）。

4.1 基本分组

SELECT
    customer_id,
    COUNT(order_id) AS order_count
FROM
    orders
GROUP BY
    customer_id;

4.2 分组和筛选

SELECT
    customer_id,
    SUM(order_total) AS total_spent
FROM
    orders
GROUP BY
    customer_id
HAVING
    total_spent > 1000;

5. 窗口函数（Window Functions）

窗口函数用于在结果集的每一行上进行计算，能够执行复杂的分析任务。MySQL 8.0+ 支持窗口函数。

5.1 排名函数

SELECT
    order_id,
    order_date,
    RANK() OVER (PARTITION BY customer_id ORDER BY order_total DESC) AS rank
FROM
    orders;

5.2 移动平均

SELECT
    order_date,
    order_total,
    AVG(order_total) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM
    orders;

6. 递归查询（Recursive Query）

递归查询用于处理层次结构数据，如组织结构或产品类别。

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_id
    FROM
        categories
    WHERE
        parent_id IS NULL
    
    UNION ALL
    
    SELECT
        c.category_id,
        c.category_name,
        c.parent_id
    FROM
        categories c
    INNER JOIN
        category_tree ct
    ON
        c.parent_id = ct.category_id
)
SELECT
    * 
FROM
    category_tree;

7. 复杂条件

7.1 CASE 表达式

CASE 表达式用于在 SQL 查询中实现条件逻辑。

SELECT
    order_id,
    CASE
        WHEN order_total < 100 THEN 'Low'
        WHEN order_total BETWEEN 100 AND 500 THEN 'Medium'
        ELSE 'High'
    END AS order_category
FROM
    orders;