Helpex - Trao đổi & giúp đỡ
Đăng nhập
10

Đây là phiên bản đơn giản hóa của vấn đề tôi đang gặp phải trong PostgreSQL.

Tôi có bảng A sau:

[ID INTEGER | SỐ GIÁ TRỊ (10,2) | LIÊN KẾT PHỤ HUYNH ]

Trong đó 'PARENT' là FK tự tham chiếu đến ID cột.

Định nghĩa bảng là:

CREATE TABLE A(ID INTEGER IDENTITY, VALUE NUMERIC(10,2), PARENT INTEGER)                                    
ALTER  TABLE A ADD CONSTRAINT FK FOREIGN KEY (PARENT) REFERENCES A(ID)

Bảng đơn giản này cho phép người ta xác định cấu trúc dữ liệu cây có độ sâu tùy ý. Bây giờ tôi cần viết một SQL (tôi không muốn sử dụng PL-SQL phía máy chủ) báo cáo cho mỗi nút, tổng giá trị của cây con "treo" dưới nó. Ví dụ, với bảng sau:

|  ID  | VALUE | PARENT |
-------------------------
|   1  | NULL  | NULL   |
|   2  | 3.50  |    1   |
|   3  | NULL  | NULL   |
|   4  | NULL  |    3   |
|   5  | 1.50  |    4   |
|   6  | 2.20  |    4   |

Tôi sẽ nhận được tập hợp kết quả sau:

| ID  |  Total-Value-of-Subtree |
|  1  |                  3.50   |
|  2  |                  3.50   |
|  3  |                  3.70   |
|  4  |                  3.70   |
|  5  |                  1.50   |
|  6  |                  2.20   |

Nói một cách đơn giản, bạn có thể giả sử rằng chỉ các nút lá mới có giá trị, các nút không phải lá luôn có giá trị NULL trong cột GIÁ TRỊ . Có cách nào để làm điều này trong SQL, thậm chí sử dụng các phần mở rộng dành riêng cho PostgreSQL không?

10 hữu ích 0 bình luận 15k xem chia sẻ
2 trả lời 2
Viết câu trả lời
7

Trong PostgreSQL, bạn có thể sử dụng CTE đệ quy (Biểu thức bảng thông thường) để đi cây trong các truy vấn của mình.

Đây là hai liên kết có liên quan đến tài liệu:

BIÊN TẬP

Vì không yêu cầu lựa chọn con nên nó có thể chạy tốt hơn một chút trên tập dữ liệu lớn hơn truy vấn của Arion.

WITH RECURSIVE children AS (
    -- select leaf nodes
    SELECT id, value, parent
        FROM t
        WHERE value IS NOT NULL
    UNION ALL
    -- propagate values of leaf nodes up, adding rows 
    SELECT t.id, children.value, t.parent
        FROM children JOIN t ON children.parent = t.id
)
SELECT id, sum(value) 
    FROM children 
    GROUP BY id   -- sum up appropriate rows
    ORDER BY id;
7 hữu ích 0 bình luận chia sẻ
4

Có thể như thế này:

WITH RECURSIVE CTE
AS
(
    SELECT
        t.ID,
        t.VALUE,
        t.PARENT
    FROM
        t
    WHERE NOT EXISTS
        (
            SELECT NULL FROM t AS t2 WHERE t2.PARENT=t.ID
        )
    UNION ALL
    SELECT
        t.ID,
        COALESCE(t.VALUE,CTE.VALUE),
        t.PARENT
    FROM
        t
        JOIN CTE
            ON CTE.PARENT=t.ID
)
SELECT
    CTE.ID,
    SUM(CTE.VALUE)
FROM
    CTE
GROUP BY
    CTE.ID
ORDER BY 
    ID;

Điều này sẽ bắt đầu với những đứa trẻ không có con. Sau đó lên cây với bố mẹ. Kết quả sẽ như thế này:

1   3.50
2   3.50
3   3.70
4   3.70
5   1.50
6   2.20
4 hữu ích 0 bình luận chia sẻ
loading

Đăng nhập để trả lời câu hỏi

Không tìm thấy câu trả lời bạn tìm kiếm? Duyệt qua các câu hỏi được gắn thẻ postgresql crash pgadmin , hoặc hỏi câu hỏi của bạn.

Có thể bạn quan tâm

loading
Hoạt động
Liên quan
Mới tạo