MySQL加入UnIQUE

Question

我需要从两个表中提取记录(classifieds_items和attachments)。分类项目的图像存储在不同的表(附件)中，并且每个分类可以有多个图像。我只需要为每个分类项目拉出一张图片。以下语句的问题是，它将复制具有多个图像的项目的结果。

$sql = "SELECT * 
          FROM classifieds_items
          JOIN (attachments) ON (attachments.attach_rel_id = classifieds_items.item_id)
         WHERE active = 1 
           AND open = 1 
           AND date_expiry > ". time()." 
           AND attachments.attach_rel_module = 'classifieds' 
      ORDER BY RAND() 
         LIMIT 4";   

$rs = mysql_query($sql);    

if(mysql_num_rows($rs)>0) {
  while($row=mysql_fetch_array($rs)) {
  $dtl_list .="<div class='fclass'>
                 <span class='fctitle'><a class='albumlnk' href='#'>".stripslashes($row['name']). '</a></span><br />
                 <img src="uploads/'.stripslashes($row['attach_thumb_location']).'" /><br />
                 '.stripslashes($row['price'])."
               </div>";
  }
}

echo $dtl_list;

Answer 1

在mysql中，您只需添加GROUP BY classifieds_items.item_id：

`$sql = "SELECT *   
          FROM classifieds_items  
          JOIN (attachments) ON (attachments.attach_rel_id = classifieds_items.item_id)  
         WHERE active = 1   
           AND open = 1   
           AND date_expiry > ". time()."   
           AND attachments.attach_rel_module = 'classifieds'   
       GROUP BY classifieds_items.item_id  
      ORDER BY RAND() 
         LIMIT 4";`