意外的查询结果，尽管数据是正确的。

I've following two tables for a messaging feature -

我为一个消息传递特性提供了两个表。

message2_recips table :

message2_recips表:

mid seq uid status
1   1   1   N
1   1   503 A

And message2 table

和message2表

mid seq created_on           created_on_ip  created_by  body
1   1   2013-08-08 19:17:44   1.2.2.1       503          some_random_text

I'm firing this query -

我要发射这个查询。

SELECT m.mid, m.seq, m.created_ON, m.created_by, m.body, r.status 
        FROM message2_recips r
INNER JOIN message2 m ON m.mid=r.mid AND m.seq=r.seq 
WHERE r.uid=503 AND r.status in ('A', 'N') AND r.seq=(SELECT max(rr.seq) 
        FROM message2_recips rr 
WHERE rr.mid=m.mid AND rr.status in ('A', 'N')) AND IF (m.seq=1 AND m.created_by=503, 1=0, 1=1)
ORDER BY created_ON DESC

Expected Result is -

预期的结果是,

mid seq created_on                  created_by  body                status
1   1   2013-08-08 19:17:44          503        some_random_text    A

But it returns zero results

但是它返回零结果。

But, if I remove if condition IF (m.seq=1 AND m.created_by=503, 1=0, 1=1) from query I get correct results. But condition in query, after observing data in message2 table satisfies and should work.

但是，如果我移除if条件(m。seq = 1和m。created_by=503, 1=0, 1=1)从查询得到正确的结果。但在查询条件下，观察message2表中的数据满足并应该工作。

I can't figure out the possible reason. Any pointers regarding what's going wrong and how to correct will be very helpful.

我想不出可能的原因。任何关于哪里出错和如何正确的指针将是非常有用的。

The said query does following,

该查询执行以下操作，

It gets conversation originated/received by current user, gets message content from other table. It gets message with status other than Delete which is Active or New. It filters our conversation with newest message with maximum sequence number. Each message does have own sequence number with increasing fashion.

由当前用户发起/接收对话，从其他表获取消息内容。它获取的消息与状态以外的状态，而非删除活动或新的。它以最大序列号过滤我们的对话。每条消息都有自己的序列号，并且越来越流行。

This does work but as this is for Inbox feature of messaging, I want user to be able to see conversation initiated by himself. Right now with above query not working properly, he can see conversation initiated by him only after he receives a reply from other users.

这确实有用，但由于这是用于消息传递的Inbox特性，我希望用户能够看到由他自己发起的对话。现在上面的查询不能正常工作，他只能在收到其他用户的回复后才能看到他的对话。

1 个解决方案

#1

Your expected result matches the criteria for the IF:

你的预期结果符合IF:

IF (m.seq=1 AND m.created_by=503, 1=0, 1=1)

So the above expression returns 1=0 which is false so that row is ignored.

因此上面的表达式返回1=0，这是错误的，所以这一行被忽略。

More readable may be to simply add this to the WHERE clause:

更可读的可能是简单地将其添加到WHERE子句:

AND NOT (m.seq=1 AND m.created_by=503)

Remove the NOT if your intention was for the original IF to be true.

如果你的初衷是为了原创，那就把它去掉。

#1