连接两个一对多表重复记录-Java 学习之路

我有3张 table ， Transaction, Transaction_Items and Transaction_History .

Transaction 是父表的位置，而 Transaction_Items and Transaction_History 是子表，其中 one to many relationship .

当我尝试将这些表连接在一起时，如果我有 2+ Transaction_History 记录，或 2+ Transaction_Items 我得到重复或三重记录结果 .

这是我目前使用的SQL查询有效，但令我担心的是，将来如果我必须加入另一个 one-to-many 表，它将再次复制结果 .

I found a workaround for this, but i was just wondering if there is a better and cleaner way to do this ?

结果应该是PostgreSQL JSON数组，它将包含Transaction_Items和Transaction_History

SELECT
    TR.id AS transaction_id,
    TR.transaction_number,
    TR.status,
    TR.status AS status,
    to_json(TR_INV.list),
    COUNT(TR_INV) item_cnt,
    COUNT(THR) tr_cnt,
    json_agg(THR)
FROM transaction_transaction AS TR
LEFT JOIN (
    SELECT
        array_agg(t) list, -- this is a workaround method
        t.transaction_id
    FROM (
        SELECT
            TR_INV.transaction_id      transaction_id,
            IT.id,
            IT.stock_number,
            CAT.key                    category_key,
            ITP.description            description,
            ITP.serial_number          serial_number,
            ITP.color                  color,
            ITP.manufacturer           manufacturer,
            ITP.inventory_model        inventory_model,
            ITP.average_cost           average_cost,
            ITP.location_in_store      location_in_store,
            ITP.firearm_caliber        firearm_caliber,
            ITP.federal_firearm_number federal_firearm_number,
            ITP.sold_price             sold_price
        FROM transaction_transaction_item TR_INV
            LEFT JOIN inventory_item IT ON IT.id = TR_INV.item_id
            LEFT JOIN inventory_itemprofile ITP ON ITP.id = IT.current_profile_id
            LEFT JOIN inventory_category CAT ON CAT.id = ITP.category_id
            LEFT JOIN inventory_categorytype CAT_T ON CAT_T.id = CAT.category_type_id
    ) t
    GROUP BY t.transaction_id
) TR_INV ON TR_INV.transaction_id = TR.id
LEFT JOIN transaction_transactionhistory THR ON THR.transaction_id = TR.id
    AND (THR.audit_code_id = 44 OR THR.audit_code_id = 27 OR THR.audit_code_id = 28)
WHERE TR.store_id = 21
    AND TR.transaction_type = 'Pawn_Loan' AND TR.date_made >= '2018-10-08'
GROUP BY TR.id, TR_INV.list

1 回答

您可以通过不使用连接来实现您想要做的事情，如下所示 . 因为你的实际表有很多列，我不知道也不应该关心 . 我刚刚创建了最简单的形式用于演示 .

CREATE TABLE transactions (
     tid    serial PRIMARY KEY,
     name   varchar(40) NOT NULL 
);

CREATE TABLE transaction_histories (
     hid    serial PRIMARY KEY ,
     tid    integer REFERENCES transactions(tid),
     history   varchar(40) NOT NULL 
);

CREATE TABLE transaction_items (
     iid    serial PRIMARY KEY ,
     tid    integer REFERENCES transactions(tid),
     item   varchar(40) NOT NULL 
);

INSERT INTO transactions(tid,name) Values(1, 'transaction');

INSERT INTO transaction_histories(tid, history) Values(1, 'history1');
INSERT INTO transaction_histories(tid, history) Values(1, 'history2');

INSERT INTO transaction_items(tid, item) Values(1, 'item1');
INSERT INTO transaction_items(tid, item) Values(1, 'item2');


select
t.*,

(select count(*) from transaction_histories h where  h.tid= t.tid) h_count ,
(select json_agg(h) from transaction_histories h where  h.tid= t.tid) h ,

(select count(*) from transaction_items i where  i.tid= t.tid) i_count ,
(select json_agg(i) from transaction_items i where  i.tid= t.tid) i

from transactions t;

回复于 2024-05-19T08:59:07+08:00

连接两个一对多表重复记录

1 回答

相关问题