我有个人信息的下表:
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,sender
和recipient
是用户登录,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_rec
或del_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
这似乎起到了作用,但有点过头了吗?
对于多个消息
ashley
s,这将失败,以避免代价高昂的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/