第5章：PostgreSQL高级查询技术

老大白菜

于 2025-01-14 00:45:00 发布

阅读量249

点赞数 3

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/hzether/article/details/145020123

第5章：PostgreSQL高级查询技术

5.1 复杂查询

多表连接

-- 内连接
SELECT 
    e.name, 
    d.department_name
FROM 
    employees e
INNER JOIN 
    departments d ON e.department_id = d.id;

-- 左外连接
SELECT 
    e.name, 
    o.order_count
FROM 
    employees e
LEFT JOIN (
    SELECT 
        employee_id, 
        COUNT(*) as order_count
    FROM 
        orders
    GROUP BY 
        employee_id
) o ON e.id = o.employee_id;

子查询

-- 标量子查询
SELECT 
    name, 
    salary
FROM 
    employees
WHERE 
    salary > (
        SELECT AVG(salary) 
        FROM employees
    );

-- 相关子查询
SELECT 
    d.department_name,
    (SELECT AVG(salary) 
     FROM employees e 
     WHERE e.department_id = d.id