短路逻辑评估算子

短路逻辑评估算子

请记住，查询不是强制执行的。您编写的查询可能在多个线程上运行，因此where子句中的短路运算符不会仅导致一个结果。

而是，使用

LIMIT

子句仅返回第一行。

SELECt * FROM quantitycacheWHERe bookstore_id = 1 OR city_id = 1 OR country_id = 1ORDER BY bookstore_id IS NULL ASC,         city_id IS NULL ASC,         country_id IS NULL ASCLIMIT 1;

---

要获得结果集中所有书籍的最佳匹配，请将结果保存到临时表中，找到最佳结果，然后返回有趣的字段。

CREATE TEMPORARY TABLE results (id int, book_id int, match_rank int);INSERT INTO results (id, book_id, match_rank)SELECt id, book_id,     -- this assumes that lower numbers are better    CASE WHEN Bookstore_ID is not null then 1          WHEN City_ID is not null then 2          ELSE 3 END as match_rankFROM quantitycacheWHERe bookstore_id = 1 OR city_id = 1 OR country_id = 1;Select * from (    select book_id, MIN(match_rank) as best_rank     from results     group by book_id) as rinner join results as rid     on r.book_id = rid.book_id     and rid.match_rank = r.best_rankinner join quantitycache as q on q.id = rid.id;DROP TABLE results;