使用 DISTINCT 的 MySQL 查询从许多可能的选项中返回一个

Question

很难为这个标题想出一个好的标题。我有一个查询，从我的表中选择产品。每个产品可能属于多个类别。因此，当我将“类别”添加到 DISTINCT 查询中时，它有时会返回多个相同产品，因为该产品属于多个类别。我希望我的查询只选择其中一个类别并返回该类别，无论是哪个类别。这是我的查询:

SELECT DISTINCT ci.NAME, 
   ci.pid, 
   ci.description, 
   ci.price, 
   ci.saleprice, 
   ci.ingredients, 
   ci.allergens AS allergens, 
   ci.isfood, 
   ci.quantity, 
   ci.ismostpopular, 
   ci.activedate, 
   ci.isfrozen, 
   cc.NAME      AS category 
FROM   cart_category cc 
   JOIN cart_item_category cic 
     ON cc.id = cic.catid 
   JOIN cart_item ci 
     ON cic.itemref = ci.itemref 
WHERE  cc.active = 1 
   AND ci.active = 1 
   AND ci.isdeleted = 0 
   AND ci.isfrozen = 0 
   AND ( ci.NAME LIKE '%dark%' 
          OR ci.pid = '%dark%' ) 
   AND ci.quantity > 5 
   AND cc.NAME <> "nutritionals"; 

当我添加cc.name as category时是指返回多个相同产品的情况。

Answer 1

尝试限制它: ... 限制 1