匹配具有多個條件的行陣列

我有帶有以下列的表事務和事務操作。transactionaction 中的 transactionid 是事務的外鍵。

我需要找到包含用戶傳遞的所有操作的每個事務。重要的是要注意，某些操作只能根據 actionType（操作 2、3）進行過濾，有些還可以根據值（操作 1）進行過濾。因此，如果用戶想要找到具有操作 2,3 的交易，他應該獲得交易 1 和 2。對于這種情況，在此答案https://stackoverflow.com/a/41140629/12035106 的幫助下，我創建了此查詢。

SELECT * from transaction
WHERE transactionid in (
    SELECT transactionid
    FROM public.transactionaction
    group by transactionid
    having array_agg(actiontype) @> array[2,3]
)

但是，操作 1 需要考慮值，因為 value == null 的操作 1 與 value != null 的操作不同。在這種情況下，我無法真正使用先前的查詢。我想出了這個查詢。

SELECT * from transaction
WHERE transactionid in (
    SELECT transactionid
    FROM public.transactionaction
    group by transactionid
    having array_agg(actiontype) @> array[2,3]
) AND transactionid in (
    SELECT transactionid
    FROM public.transactionaction
    WHERE actiontype = 1 AND value is not null
)

這一項作業，因此，我只會得到事務 2，但我覺得我過于復雜了，因為這個查詢多次回圈遍歷同一個表。我在 actiontype 上創建了一個索引，所以查詢計劃看起來更好，但也許有更簡單的方法來實作相同的結果？

uj5u.com熱心網友回復：

我認為您有一個初始設計問題，即使如此，我也會盡力幫助您進行查詢。

您可以使用子查詢并了解事務是否具有不同于 null 的 actionType 1（通過聚合函式和“over”以避免“group by”），這樣您就知道可以過濾 actionType 2 和3

我試著模擬你的場景

drop table if exists transaction;
create temp table transaction(
    transactionId   bigint,
    name            varchar
) on commit drop;
insert into transaction(transactionId, name) values(1, 'Trans 1');
insert into transaction(transactionId, name) values(2, 'Trans 2');

drop table if exists transactionAction;
create temp table transactionAction(
    actionId        bigint,
    transactionId   bigint,
    actionType      bigint,
    value           bigint
) on commit drop;
insert into transactionAction(actionId, transactionId, actionType, value) values(1, 1, 1, null);
insert into transactionAction(actionId, transactionId, actionType, value) values(2, 1, 2, null);
insert into transactionAction(actionId, transactionId, actionType, value) values(3, 1, 3, null);

insert into transactionAction(actionId, transactionId, actionType, value) values(4, 2, 1, 1);
insert into transactionAction(actionId, transactionId, actionType, value) values(5, 2, 2, null);
insert into transactionAction(actionId, transactionId, actionType, value) values(6, 2, 3, null);

查詢將是

select
    distinct
    t.*
from
    (
        select
            ta.actionid,
            ta.transactionId,
            ta.actionType,
            ta.value,
            /*sum only if action type 1 is different from null*/
            sum(case when ta.actionType = 1 and not ta.value is null then 1 else 0 end) over(partition by ta.transactionId) haveActionOne
        from
            transactionAction ta
    ) a
    inner join transaction t
        on a.transactionId = t.transactionId
where   
        /*indicates if the transaction in its actions has type 1 different from null in its value*/
        a.haveActionOne = 1
    and a.actionType in (2,3);

問候，

uj5u.com熱心網友回復：

作為一種選擇，在 Where 子句中移動操作 (1) 的條件將是一種優雅的選擇，因此這種方法消除了任何子查詢。

Select Transaction.transactionid, Max(Transaction.name)
From Transaction Inner Join Transactionaction On (Transaction.transactionid=Transactionaction.transactionid)
Where (actiontype=1 And value Is Not Null) Or actiontype<>1 
Group by Transaction.transactionid
Having array_agg(actiontype Order by actiontype) = array[1,2,3]

標籤：sql PostgreSQL

上一篇：將json陣列插入postgres的問題：格式錯誤

下一篇：切換PC后SpringBoot專案不起作用