mysql查询语句where后面加if判断
说明:WHERE IF(条件, true执行条件, false执行条件 )
SELECT * FROM book WHERE IF(price > 120, btypeid=10, btypeid=11);
以上sql查询语句,意思是查price字段大于120,当price大于120为真时,再找btypeid等于10的行记录,
当price大于120为假时,再找btypeid等于11的行记录。什么意思呢?咱们换句话说吧!
意思就是当price大于120为真时,执行语句为where price>120 and btypeid=10;
当price大于120为假时,执行语句为where price<120 and btypeid=11;准确来说应该是where price<=120 and btypeid=11;
其实说白了,SELECT * FROM book WHERE IF(price > 120, btypeid=10, btypeid=11);这条sql语句等价于如下sql语句
select * from book where price > 120 and btypeid =10 unionselect * from book where price < 120 and btypeid = 11;
准确的来说是select * from book where price > 120 and btypeid =10 unionselect * from book where price <=120 and btypeid = 11;
以下sql语句同理,只不过是多了一个limit 0,1;取前面1条记录。
SELECT * FROM book WHERE IF(price > 120, btypeid=10, btypeid=11) limit 0,1;