mysql - 在一个别名中选择具有两个条件的计数

我的sql表具有以下结构：

id, sender, recipient, content, date_sent, last_activity, sender_read, recipient read

它用于个人消息。 sender行是发送消息的用户的ID。当他收到答复时，sender_read变为1，recipient也是如此。我需要计算用户拥有的未读邮件的总数。我尝试过这样的事情：

SELECT COUNT(CASE WHEN recipient = ? AND recipient_read = 0 THEN 1 END) as unread FROM pm
UNION SELECT COUNT(CASE WHEN sender = ? AND sender_read = 0 THEN 1 END) as unread FROM pm

但这似乎不起作用。基本上，我需要结合两个计数并返回一个别名。我怎样才能做到这一点？

最佳答案

只需将2个条件组合成一个case表达式：

SELECT COUNT(CASE WHEN recipient = ? AND recipient_read = 0 THEN 1
                  WHEN sender = ? AND sender_read = 0 THEN 1
                  ELSE null
             END) as unread FROM pm
WHERE recipient = ? OR sender = ?