連接多個表并在子查詢PostgreSQL中獲取MAX值

我在 PostgreSQL 中有 4 個表，結構如下：

  "Customers"
    ID   |     NAME 
    101        Max 
    102        Peter
    103        Alex


"orders"
ID | customer_id |   CREATED_AT  
1         101         2022-05-12       
2         101         2022-06-14       
3         101         2022-07-9         
4         102         2022-02-14       
5         102         2022-06-18       
6         103         2022-05-22 

"orderEntry"
ID |   order_id |   product_id |  
1         3             10       
2         3             20       
3         3             30         
4         5             20       
5         5             40       
6         6             20        

"product"
ID |   min_duration
10         P10D                   
20         P20D        
30         P30D         
40         P40D       
50         P50D      

1. 首先，我需要max(created_at)為每個客戶選擇帶有日期的“訂單”，這是通過查詢完成的（它有效！）：

                SELECT  c.id as customerId,
                        o.id as orderId,
                        o.created_at
                FROM    Customer c
                        INNER JOIN Orders o
                            ON c.id = o.customer_id
                        INNER JOIN
                                (
                                    SELECT  customer_id, MAX(created_at) Max_Date
                                    FROM    Orders
                                    GROUP   BY customer_id
                                ) res ON  o.customer_id = res.customer_id AND
                                        o.created_at = res.Max_date

結果將如下所示：

customer_id |   order_id  |   CREATED_AT       
    101            3          2022-07-9             
    102            5          2022-06-18       
    103            6          2022-05-22 

2. 其次，我需要從“orderEntry”表中為每個 order_id 選擇，“products”max(min_duration)的結果應該是：

      order_id |   max(min_duration)  
         3             P30D       
         5             P40D       
         6             P20D  

然后通過“order_id”加入1）和2）查詢的結果，我試圖得到的總結果應該是這樣的：

 customer_name |      customer_id |  Order_ID |   Order_CREATED_AT |      Max_Duration
       Max               101            3          2022-07-9                 P30D
       Peter             102            5          2022-06-18                P40D
       Alex              103            6          2022-05-22                P20D

我正在努力獲取 2) 的查詢，然后使用 1) 的查詢加入所有內容以獲得結果。任何幫助我將不勝感激！

uj5u.com熱心網友回復：

您可以對 CTE 進行第一個查詢并使用它來加入其余查詢。

像這樣。

WITH CTE AS (                SELECT  c.id as customerId,
                        o.id as orderId,
                        o.created_at
                FROM    Customer c
                        INNER JOIN Orders o
                            ON c.id = o.customer_id
                        INNER JOIN
                                (
                                    SELECT  customer_id, MAX(created_at) Max_Date
                                    FROM    Orders
                                    GROUP   BY customer_id
                                ) res ON  o.customer_id = res.customer_id AND
                                        o.created_at = res.Max_date)
SELECT customerId,orderId,created_at,p.min_duration
FROM CTE 
    JOIN (SELECT "orderId", MAX("product_id") as product_id FROM "orderEntry" GROUP BY orderId) oe ON CTE.orderId = oe.orderId
    JOIN "product" pr ON oe.product_id  = pr."ID"

標籤：sql PostgreSQL

上一篇：表選擇一列并分成兩個不同的

下一篇：postgres中json_agg內的條件