基于id和另一个布尔列计数的SQL查询

I have the following table that tracks chat message logs

我有下表跟踪聊天消息日志

CREATE TABLE public.perm_message_log
(
    id bigint NOT NULL DEFAULT nextval('perm_message_log_id_seq'::regclass),
    message text,
    from_id text,
    to_id text,
    match_id text,
    unix_timestamp bigint,
    own_account boolean,
    reply_batch boolean DEFAULT false,
    insert_time timestamp with time zone DEFAULT now(),
    account_id bigint,
    match_pk bigint
)

each conversation has the same match_id I want to construct a SQL query that would return the match_ids only if there are less than two reply_batch = true for the messages with the same match_id

每个会话具有相同的match_id我想构建一个SQL查询，只有当具有相同match_id的消息少于两个reply_batch = true时才返回match_ids

I hope I was able to write it in a clear way enough.

我希望我能够以清晰的方式写出来。

Each row that contains a message has the column reply_batch which is set to true or false. I want the query to return all the match_ids that have less than two "true" booleans for the reply_batch column.

包含消息的每一行都包含reply_batch列，该列设置为true或false。我希望查询返回所有match_batch列中包含少于两个“true”布尔值的match_ids。

EDIT @TimBiegeleisen

编辑@TimBiegeleisen

Thanks for your answer, what if I were to delete the rows your answer returns in another table? I came up with the query below but it's wrong.

感谢您的回答，如果我要删除您的答案在另一个表中返回的行，该怎么办？我想出了下面的查询，但这是错的。

delete from already_matched where already_matched.userid = (
WITH cte AS (
    SELECT match_id
    FROM public.perm_message_log
    GROUP BY match_id
    HAVING SUM(CASE WHEN reply_batch THEN 1 ELSE 0 END) = 0
)
SELECT  t1.from_id
FROM public.perm_message_log t1
INNER JOIN cte t2
    ON t1.match_id = t2.match_id
WHERE NOT t1.own_account)

1 个解决方案

#1

You can try aggregating your table by the match_id, counting the number of times which reply_batch be true for that match_id. If this count is less than two, then retain that match_id in the result set.

您可以尝试通过match_id聚合您的表，计算该match_id的reply_batch为true的次数。如果此计数小于2，则在结果集中保留match_id。

WITH cte AS (
    SELECT match_id
    FROM public.perm_message_log
    GROUP BY match_id
    HAVING SUM(CASE WHEN reply_batch THEN 1 ELSE 0 END) < 2
)
SELECT t1.match_id,
       t1.from_id
FROM public.perm_message_log t1
INNER JOIN cte t2
    ON t1.match_id = t2.match_id
WHERE NOT t1.own_account

Edit:

编辑：

If you want to use the set of match_id returned by the CTE to delete certains rows in another table, you can try:

如果要使用CTE返回的match_id集来删除另一个表中的某些行，可以尝试：

DELETE
FROM already_matched
WHERE match_id IN (SELECT t.match_id FROM cte t)

#1