我有个人信息的下表:

id*    sender*    recipient* del_by_rec del_by_send
-+----------+-------------+-----------+-----------+
 2     tom        beth            0          1
 5     tom        josh            0          1
 5     tom        chris           1          1
 7     ashley     burt            1          0
 8     jenna      tom             1          0
 8     jenna      ashley          0          0
 8     jenna      jenna           1          0
10     ashley     burt            0          0

哪里
id是消息id,
senderrecipient是用户登录,
sender是消息的唯一id
消息可以有多个recipient
id是1,如果收件人已从其收件箱中删除邮件,并且
del_by_rec是1,如果发件人已从其发件箱中删除邮件。
del_by_send-id-sender是主键,并且
recipient引用包含消息的另一个表中的主键。
我需要确定当前用户决定从表中删除邮件是否安全:
从收件人的收件箱:
如果发件人已经从其发件箱中删除了邮件(id=1);并且
如果此用户是唯一尚未从其收件箱中删除此邮件的收件人(对于此用户,id=0,对于所有其他收件人,del_by_send=1)
或从发件人的发件箱:
如果所有收件人都已从其收件箱中删除此邮件(对于此邮件的所有收件人,del_by_rec=1)。
否则,当前用户只需将其相应的del_by_recdel_by_rec标志设置为1,即可将此消息标记为删除。
有没有办法有效地查询这个条件
查看当前消息的当前用户;或
当前用户批量删除多条消息(我认为在这种情况下,将返回多行结果)。
如果返回boolean/int,那就太棒了。
出于对我的爱,我无法通过此查询(假设用户del_by_rec,并且她希望从收件箱中删除消息del_by_send):
SELECT (
    (SUM(del_by_send) > 0) # sender has deleted (at least 1)
    && ((SUM(del_by_rec) + 1) >= COUNT(id)) # only 1 recipient has not deleted yet
    && ((SELECT del_by_rec FROM msg_meta WHERE recipient = 'ashley' AND id = 8) = 0) # and that recipient is this user
)
FROM msg_meta
WHERE id = 8
GROUP BY id

这似乎起到了作用,但有点过头了吗?
对于多个消息ashleys,这将失败,以避免代价高昂的8。我试过为收件人id设置foreach,但我无法将其处理到子查询中。。
救命啊。谢谢你们。。

最佳答案

我的建议是使用子查询在消息级别获取所需的信息,然后对其应用逻辑。以下查询结束:

select id, (case when sum_del_by_send > 0 and (num_rec - sum_del_by_rec) <= 1
                 then 'Y'
                 else 'N'
            end) as IsSafeToDelete
from (select id,
             sum(del_by_send) as sum_del_by_send,
             sum(del_by_rec) as sum_del_by_rec,
             count(*) as num_rec
      from msg_meta
      group by id
     ) m

这不考虑当前收件人。这种变体:
select id, (case when sum_del_by_send > 0 and (num_others - sum_del_by_others) = 1
                 then 'Y'
                 else 'N'
            end) as IsSafeToDelete
from (select id,
             sum(del_by_send) as sum_del_by_send,
             sum(case when recipient <> @RECIPIENT then del_by_rec end) as sum_del_by_others,
             sum(case when recipient <> @RECIPIENT then 1 else 0 end) as num_others,
             count(*) as num_rec
      from msg_meta
      group by id
     ) m

这将同时处理所有消息。要处理特定的消息id,只需将子查询中的“按id分组”替换为:
where id = @ID

(@RECIPIENT和@ID是要自定义查询的值。)

关于mysql - 如何从MySQL表查询复杂的 boolean 条件,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/11882115/

10-12 07:34
查看更多