我有这个SQL select查询

SELECT *
FROM bots
WHERE id NOT IN (select botid
                 from messages
                 where messages.recipient = :recipient)
limit 1

我需要这样做:
SELECT *
FROM bots
[if isset(recipient = $recipient AND sender = $sender)]
  WHERE id IN (select botid
               from messages
               where messages.recipient = :recipient and sender = :sender)
else
  WHERE id NOT IN (select botid
                   from messages
                   where messages.recipient = :recipient)
limit 1

[]-伪代码
如何进行此查询?
机器人程序表:
-------------------------------------------------------
| id       |  auth_token   |  messages_today  | vkid  |
-------------------------------------------------------
| 1       |  token   |                    0   | 2323  |
-------------------------------------------------------
| 2       |  token   |                     0  | 2343  |
-------------------------------------------------------

消息表:
-----------------------------------------------------------
| id       |  recipient   |  sender  | botid  | messageid |
-----------------------------------------------------------
| 1        |  12          |        1  |     1  |      3322 |
-----------------------------------------------------------
| 2        |  12          |        2  |     2  |    332123 |
-----------------------------------------------------------
| 3        |  13          |        1  |     1  |    332123 |
-----------------------------------------------------------

最佳答案

使用外部连接将
从bots表返回所有bots
以及来自消息talbe的匹配数据
但是我们排除了只返回机器人程序的匹配数据
对于特定的BOT,不存在具有接收者和发送者的消息。
返回所有机器人程序

Select * from bots B
LEFT JOIN messages M
  on M.BotID = B.ID
 and M.Sender = $sender
 and M.Recipient = $Recipient
Where M.BotID is null

关于mysql - WHERE子句中的MySQL和if/else语句,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/34319408/

10-11 02:23