寻求正确的MySql "IF"方法的指南

Question

我需要获取特定列值的数据，但也包括启用了inventory且stock大于0的项IF。

以下只是一个概念查询，以便直观地了解我的意图。当然它不起作用。

SELECT id, name, image, price
FROM shop
WHERE published = 1
AND price > 2
/*concept clause here*/
IF(inventory = 1 AND stock > 0, include, ignore)
/*end concept*/
ORDER BY RAND() 
LIMIT 30

表格结构

shop
__________________________________________
|id|name  |image   |price|inventory|stock|
------------------------------------------
|4 |widget|pic.jpg |6    |1        |12   |
------------------------------------------
|5 |bolt  |bolt.jpg|7    |0        |0    |
------------------------------------------
|6 |trowel|trow.jpg|12   |1        |0    |
__________________________________________

期望的结果

阵列

[
['id'=>4,'name'=>'widget','image'=>'pic.jpg','price'=>6],
['id'=>5,'name'=>'bolt','image'=>'bolt.jpg','price'=>7]
]

启用了inventory但stock值为0的行将不在数组中。

在这种情况下，我如何才能取得结果

---

正在工作；脏的"；解决方案

我已经运行了两次类似的查询，然后合并数组

获取未启用库存的物品

SELECT id, name, image, price
FROM shop
WHERE published = 1
AND price > 2
AND inventory = 0 
ORDER BY RAND() 
LIMIT 30

获取启用库存并有库存的物品

SELECT id, name, image, price
FROM shop
WHERE published = 1
AND price > 2
AND inventory = 1 
AND stock > 0 
ORDER BY RAND() 
LIMIT 30

合并阵列

$merged = array_merge($withoutInventory, $withInventory);

Answer 1

样本数据和预期结果将有助于澄清。我认为你需要把你的专栏选择和放在一起

SELECT id, name, image, price
FROM shop
WHERE (published = 1 AND price > 2) OR
(inventory = 1 AND stock > 0)
ORDER BY RAND() 
LIMIT 30