PostgreSQL复杂嵌套查询SQL示例

最新推荐文章于 2024-09-13 23:04:22 发布

.NET跨平台

最新推荐文章于 2024-09-13 23:04:22 发布

阅读量931

点赞数 23

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

本文链接：https://blog.csdn.net/WuLex/article/details/138096308

版权

Postgresql数据库专栏收录该内容

24 篇文章 5 订阅

订阅专栏

当涉及复杂嵌套查询时，PostgreSQL的强大功能允许您执行各种复杂的操作。以下是一个示例，假设我们有一个电子商务数据库，其中包含顾客、订单和订单详情表。我们想要检索出购买了某个特定产品的所有顾客的信息。假设我们有以下表结构：

customers（顾客表）包含顾客的信息，如顾客ID、姓名等。
orders（订单表）包含订单的信息，如订单ID、顾客ID等。
order_details（订单详情表）包含订单的详细信息，如产品ID、订单ID、数量等。
products（产品表）包含产品的信息，如产品ID、名称等。

下面是一个示例查询，用于检索购买了特定产品的所有顾客的信息：

SELECT c.customer_id, c.customer_name, o.order_id, od.quantity, p.product_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
WHERE p.product_name = 'YourProduct';

这个查询首先连接了customers、orders、order_details和products表，然后筛选出了产品名为"YourProduct"的所有行。最后，它选择了所需的列，包括顾客ID、顾客姓名、订单ID、订单详情中的产品数量和产品名称。

当然，根据实际情况，您可能需要调整查询以适应您的数据库架构和需求。

当涉及到更复杂的查询时，您可能需要使用子查询、窗口函数、联合查询等高级技术。下面是一个更复杂的示例，假设我们需要找到购买了至少两种不同产品的顾客，并列出他们的订单信息以及购买的产品数量：

WITH customer_orders AS (
    SELECT o.customer_id, o.order_id, COUNT(od.product_id) AS num_products
    FROM orders o
    JOIN order_details od ON o.order_id = od.order_id
    GROUP BY o.customer_id, o.order_id
),
customer_product_count AS (
    SELECT co.customer_id, COUNT(*) AS distinct_products
    FROM (
        SELECT DISTINCT od.order_id, od.product_id, co.customer_id
        FROM order_details od
        JOIN customer_orders co ON od.order_id = co.order_id
    ) AS co
    GROUP BY co.customer_id
)
SELECT c.customer_id, c.customer_name, co.order_id, cp.num_products
FROM customers c
JOIN customer_orders co ON c.customer_id = co.customer_id
JOIN customer_product_count cp ON c.customer_id = cp.customer_id
WHERE cp.distinct_products >= 2;

这个查询使用了两个通用表表达式（CTE），也称为WITH子句。第一个CTE customer_orders 统计了每个订单中的产品数量，而第二个CTE customer_product_count 统计了每个顾客购买的不同产品数量。最后的查询通过连接这些CTE并应用筛选条件来找到购买了至少两种不同产品的顾客，并列出了他们的订单信息以及购买的产品数量。

这只是一个复杂查询的示例，具体的查询将取决于您的数据库结构和业务需求。在编写更复杂的查询时，确保使用适当的索引、优化技术和测试方法，以确保查询性能良好并且准确地返回所需的结果。

下面是一个更为复杂的示例，假设我们有一个社交网络的数据库，其中包含用户、帖子和评论等表。我们想要找出每个用户的总帖子数、总评论数以及他们的平均帖子和评论数。此外，我们还希望将用户按照其总帖子数和评论数的排名进行排序。

WITH user_post_counts AS (
    SELECT
        u.user_id,
        COUNT(DISTINCT p.post_id) AS num_posts
    FROM
        users u
    LEFT JOIN
        posts p ON u.user_id = p.user_id
    GROUP BY
        u.user_id
),
user_comment_counts AS (
    SELECT
        u.user_id,
        COUNT(DISTINCT c.comment_id) AS num_comments
    FROM
        users u
    LEFT JOIN
        comments c ON u.user_id = c.user_id
    GROUP BY
        u.user_id
)
SELECT
    u.user_id,
    u.username,
    COALESCE(upc.num_posts, 0) AS num_posts,
    COALESCE(ucc.num_comments, 0) AS num_comments,
    COALESCE(upc.num_posts, 0) + COALESCE(ucc.num_comments, 0) AS total_activity,
    ROUND(COALESCE(upc.num_posts, 0)::NUMERIC / NULLIF((SELECT COUNT(*) FROM posts), 0), 2) AS avg_posts_per_user,
    ROUND(COALESCE(ucc.num_comments, 0)::NUMERIC / NULLIF((SELECT COUNT(*) FROM comments), 0), 2) AS avg_comments_per_user,
    RANK() OVER (ORDER BY COALESCE(upc.num_posts, 0) + COALESCE(ucc.num_comments, 0) DESC) AS activity_rank
FROM
    users u
LEFT JOIN
    user_post_counts upc ON u.user_id = upc.user_id
LEFT JOIN
    user_comment_counts ucc ON u.user_id = ucc.user_id;

这个查询使用了两个通用表表达式（CTE），分别计算了每个用户的总帖子数和总评论数。然后，主查询将这些计数连接到用户表，并计算了每个用户的总活动数、平均帖子数和平均评论数，并通过排名函数对用户进行排序。

这个查询考虑了一些复杂情况，比如处理可能不存在的帖子或评论，并且通过使用COALESCE函数和NULLIF函数处理了除以零的情况。

让我们进一步增加查询的复杂性。假设我们有一个在线论坛的数据库，除了用户、帖子和评论之外，还有标签和标签关联表，用于将标签与帖子相关联。我们的目标是找出拥有最多共同标签的用户对，并列出这些用户之间的共同标签及其数量。

WITH user_tag_counts AS (
    SELECT
        u.user_id,
        COUNT(DISTINCT ut.tag_id) AS num_tags
    FROM
        users u
    JOIN
        user_posts up ON u.user_id = up.user_id
    JOIN
        post_tags pt ON up.post_id = pt.post_id
    JOIN
        user_posts up2 ON pt.post_id = up2.post_id
    JOIN
        user_tags ut ON up2.user_id = ut.user_id
    WHERE
        u.user_id != up2.user_id
    GROUP BY
        u.user_id, up2.user_id
),
max_tag_counts AS (
    SELECT
        MAX(num_tags) AS max_num_tags
    FROM
        user_tag_counts
)
SELECT
    utc1.user_id AS user1_id,
    utc2.user_id AS user2_id,
    COUNT(*) AS num_common_tags
FROM
    user_tag_counts utc1
JOIN
    user_tag_counts utc2 ON utc1.user_id < utc2.user_id
JOIN
    max_tag_counts mtc ON utc1.num_tags = mtc.max_num_tags
WHERE
    utc1.num_tags = mtc.max_num_tags
GROUP BY
    utc1.user_id, utc2.user_id;

这个查询使用了两个通用表表达式（CTE）。首先，user_tag_counts CTE 找出了每对用户之间共同标签的数量。然后，max_tag_counts CTE 找出了共同标签数量的最大值。最后的查询选择具有最大共同标签数量的用户对，并计算了这些用户之间的共同标签数量。

这个查询比之前的复杂查询更进一步，需要在多个表之间进行多层连接和聚合操作，以找出共同标签最多的用户对。

让我们继续增加查询的复杂性。现在，我们考虑一个更加复杂的情景：一个在线教育平台的数据库，其中包括用户、课程、章节、测验和学习进度等表。我们的目标是找出每个用户最近学习的课程及其章节，并列出他们的学习进度。

WITH recent_user_progress AS (
    SELECT
        user_id,
        course_id,
        MAX(progress_date) AS recent_progress_date
    FROM
        user_progress
    GROUP BY
        user_id,
        course_id
),
latest_user_progress AS (
    SELECT
        up.user_id,
        up.course_id,
        up.chapter_id,
        up.progress,
        up.progress_date
    FROM
        user_progress up
    JOIN
        recent_user_progress rup ON up.user_id = rup.user_id AND up.course_id = rup.course_id AND up.progress_date = rup.recent_progress_date
),
user_course_chapter AS (
    SELECT
        u.user_id,
        uc.course_id,
        lc.chapter_id,
        lc.chapter_name
    FROM
        users u
    JOIN
        user_courses uc ON u.user_id = uc.user_id
    JOIN
        course_chapters cc ON uc.course_id = cc.course_id
    JOIN
        (SELECT
            course_id,
            MAX(chapter_order) AS max_chapter_order
        FROM
            course_chapters
        GROUP BY
            course_id) mc ON cc.course_id = mc.course_id
    JOIN
        course_chapters lc ON cc.course_id = lc.course_id AND cc.chapter_order = mc.max_chapter_order
),
user_latest_progress AS (
    SELECT
        ucp.user_id,
        ucp.course_id,
        ucp.chapter_id,
        ucp.chapter_name,
        ulp.progress,
        ulp.progress_date
    FROM
        user_course_chapter ucp
    LEFT JOIN
        latest_user_progress ulp ON ucp.user_id = ulp.user_id AND ucp.course_id = ulp.course_id
)
SELECT
    u.user_id,
    u.username,
    ulp.course_id,
    ulp.chapter_name,
    ulp.progress,
    ulp.progress_date
FROM
    users u
LEFT JOIN
    user_latest_progress ulp ON u.user_id = ulp.user_id;

这个查询使用了多个通用表表达式（CTE），涉及到多层连接和子查询。首先，通过 recent_user_progress CTE 找出每个用户最近学习的课程。然后，通过 latest_user_progress CTE 找出每个用户在最近学习的课程中的最新学习进度。接着，通过 user_course_chapter CTE 找出每个用户最近学习的课程中的最后一个章节。最后，通过 user_latest_progress CTE 将用户的最新学习进度与最后一个章节信息关联起来。

这个查询考虑了用户可能在不同课程中的学习进度，并且确保每个用户最近学习的课程和章节被正确地检索出来。