常用SQL语句大全实例总结-进阶篇

进阶查询操作

1、聚合与分组（GROUP BY + 聚合函数）

-- 统计每个地区的用户数（带分组列）
-- 语法
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name;
-- 实例
SELECT address, COUNT(user_id) as user_count 
FROM User 
GROUP BY address;

-- 获取年龄最大的用户信息
-- 语法
SELECT MAX(column_name) FROM table_name;
-- 实例
SELECT MAX(age) FROM User;

-- 同时使用多个聚合函数
SELECT address, 
       AVG(age) as avg_age,
       MIN(age) as min_age,
       MAX(age) as max_age 
FROM User 
GROUP BY address;

2、分组后过滤（HAVING）

-- 筛选用户数超过100的地区
-- 语法
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;
-- 实例
SELECT address, COUNT(user_id) as user_count 
FROM User 
GROUP BY address
HAVING COUNT(user_id) > 100;

3、表连接（JOIN）

-- 创建订单表
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    user_id VARCHAR(255),
    amount DECIMAL(10,2),
    order_date DATE
);

-- 内连接（显示匹配记录）
-- 语法
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
-- 实例
SELECT u.user_name, o.order_id, o.order_date
FROM User u
INNER JOIN Orders o
ON u.user_id = o.user_id;

-- 左连接（保留所有左表记录）
SELECT u.user_name, o.order_id
FROM User u
LEFT JOIN Orders o
ON u.user_id = o.user_id;

-- 多表连接（连接三个表）
SELECT u.user_name, o.order_id, p.product_name
FROM User u
JOIN Orders o ON u.user_id = o.user_id
JOIN Products p ON o.product_id = p.product_id;

4、子查询

-- 在WHERE中使用子查询
-- 查找订单金额高于平均值的用户
SELECT user_name 
FROM User
WHERE user_id IN (
    SELECT user_id
    FROM Orders
    WHERE amount > (SELECT AVG(amount) FROM Orders)
);

-- 在FROM中使用子查询
SELECT sub.address, sub.avg_age
FROM (
    SELECT address, AVG(age) as avg_age
    FROM User
    GROUP BY address
) sub
WHERE avg_age > 25;

5、窗口函数

-- 给用户按年龄排序添加行号
-- 语法
SELECT column_name,
       window_function() OVER (PARTITION BY column ORDER BY column)
FROM table_name;
-- 实例
SELECT user_name, age,
       ROW_NUMBER() OVER (ORDER BY age DESC) as rank,
       RANK() OVER (ORDER BY age DESC) as dense_rank
FROM User;

-- 计算各地区的累计订单金额
SELECT o.order_date, o.amount,
       SUM(o.amount) OVER (PARTITION BY u.address ORDER BY o.order_date) 
       as running_total
FROM Orders o
JOIN User u ON o.user_id = u.user_id;

6、公用表表达式（CTE）

-- 使用WITH创建临时结果集
-- 语法
WITH cte_name AS (
    SELECT columns
    FROM table
)
SELECT * FROM cte_name;
-- 实例
WITH HighValueUsers AS (
    SELECT user_id 
    FROM Orders
    GROUP BY user_id
    HAVING SUM(amount) > 10000
)
SELECT u.* 
FROM User u
JOIN HighValueUsers h ON u.user_id = h.user_id;

7、分页查询

-- MySQL分页实现
-- 语法
SELECT columns
FROM table
LIMIT row_count OFFSET offset;
-- 实例
SELECT user_name, age
FROM User
ORDER BY age DESC
LIMIT 10 OFFSET 20;  -- 跳过前20条取10条（第3页）

-- SQL Server分页
SELECT user_name, age
FROM User
ORDER BY age DESC
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

8、条件表达式（CASE）

-- 用户年龄段分类
SELECT user_name, age,
       CASE
           WHEN age < 20 THEN '青少年'
           WHEN age BETWEEN 20 AND 30 THEN '青年'
           ELSE '中年以上'
       END as age_group
FROM User;

-- 配合聚合函数使用
SELECT 
    SUM(CASE WHEN age < 20 THEN 1 ELSE 0 END) as teen_count,
    SUM(CASE WHEN age >= 60 THEN 1 ELSE 0 END) as senior_count
FROM User;