PostgreSQL 高级SQL查询（三）
1. JOIN 操作
1.1 内连接（INNER JOIN）
内连接用于返回两个表中存在匹配关系的记录。基本语法如下：
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
例如，从 users 表和 orders 表中检索所有用户及其订单信息：
SELECT users.username, orders.order_id, orders.order_date
FROM users
INNER JOIN orders ON users.id = orders.user_id;
1.2 左连接（LEFT JOIN）
左连接返回左表中的所有记录，即使右表中没有匹配的记录。基本语法如下：
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
例如，检索所有用户及其订单信息（包括没有订单的用户）：
SELECT users.username, orders.order_id, orders.order_date
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
1.3 右连接（RIGHT JOIN）
右连接返回右表中的所有记录，即使左表中没有匹配的记录。基本语法如下：
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
例如，检索所有订单及其用户信息（包括没有用户信息的订单）：
SELECT users.username, orders.order_id, orders.order_date
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;
1.4 全连接（FULL JOIN）
全连接返回左表和右表中所有匹配的记录以及不匹配的记录。基本语法如下：
SELECT columns
FROM table1
FULL JOIN table2 ON table1.column = table2.column;
例如，检索所有用户及其订单信息（包括没有订单的用户和没有用户信息的订单）：
SELECT users.username, orders.order_id, orders.order_date
FROM users
FULL JOIN orders ON users.id = orders.user_id;
2. 子查询
子查询是嵌套在另一个查询中的查询，用于复杂的查询操作。可以分为两类：标量子查询和表子查询。
2.1 标量子查询
标量子查询返回单个值，可以在 SELECT、WHERE 和 HAVING 子句中使用。
例如，查询订单金额最高的订单：
SELECT order_id, amount
FROM orders
WHERE amount = (SELECT MAX(amount) FROM orders);
2.2 表子查询
表子查询返回一个结果集，可以在 FROM 子句中使用。
例如，查询订单金额大于平均订单金额的订单：
SELECT order_id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);
3. 聚合函数与分组
3.1 常用聚合函数
COUNT(): 计数SUM(): 求和AVG(): 平均值MAX(): 最大值MIN(): 最小值
例如，查询用户总数：
SELECT COUNT(*) FROM users;
3.2 分组（GROUP BY）
GROUP BY 子句用于将结果集按照一个或多个列进行分组。基本语法如下：
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;
例如，按用户分组并计算每个用户的订单总金额：
SELECT user_id, SUM(amount) as total_amount
FROM orders
GROUP BY user_id;
3.3 过滤分组结果（HAVING）
HAVING 子句用于过滤分组后的结果集。基本语法如下：
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;
例如，查询订单总金额大于1000的用户：
SELECT user_id, SUM(amount) as total_amount
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;
4. 窗口函数
窗口函数用于在查询结果集中执行计算，类似于聚合函数，但不缩小结果集的范围。常用窗口函数包括 ROW_NUMBER()、RANK() 和 DENSE_RANK()。
例如，为每个用户的订单按金额排序：
SELECT user_id, order_id, amount,RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) as rank
FROM orders;
5. 案例实战
5.1 练习题目
- 查询每个用户的最新订单。
- 查询订单总金额排名前 5 的用户。
- 按月统计订单数量。
5.2 示例答案
- 查询每个用户的最新订单：
SELECT user_id, order_id, order_date
FROM (SELECT user_id, order_id, order_date,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) as rnFROM orders
) subquery
WHERE rn = 1;
- 查询订单总金额排名前 5 的用户：
SELECT user_id, SUM(amount) as total_amount
FROM orders
GROUP BY user_id
ORDER BY total_amount DESC
LIMIT 5;
- 按月统计订单数量：
SELECT DATE_TRUNC('month', order_date) as month, COUNT(*) as order_count
FROM orders
GROUP BY month
ORDER BY month;