mysql - 在sql语句部分使用if count

Question

SELECT 
    *
FROM
    messages
WHERE
       (messages.to = 'Jack' AND (type = 'message' OR type = 'reply'))
    OR (messages.from = 'Jack' AND type = 'reply')
    OR (messages.from = 'Jack' AND type = 'message')
ORDER BY messages.message_id DESC , messages.id DESC

我想过滤掉最后一个查询语句中的结果

(messages.from='Jack' AND type='message') 

如果它的计数等于1。

Answer 1

使用子查询，我会做这样的事情来代替最后一个条件:

messages.from = 'Jack' AND 
type = 'message' AND 
1 =(select count(primary_key) from messages /* 1=count : this would ensure that 
                                               condition works only if 
                                               1 row is returned*/
where (messages.from='Jack' AND type='message')  )

所以最终的 SQL 应该是:

SELECT 
    *
FROM
    messages
WHERE
       (messages.to = 'Jack' AND (type = 'message' OR type = 'reply'))
    OR (messages.from = 'Jack' AND type = 'reply')
    OR (messages.from = 'Jack' AND 
        type = 'message' AND 
        1 =(select count(primary_key) from messages
        where (messages.from='Jack' AND type='message')  ))

        ORDER BY messages.message_id DESC , messages.id DESC