$sql = "select distinct(ontopid),gb.id as id,f.id as fid,g.id as gid,g.*,gb.*,f.* from pk_groupbuy gb
left join pk_ontop as o on o.ontopid=gb.id
left join pk_goods g on gb.goodsid=g.id and g.status=2 and g.invalid>UNIX_TIMESTAMP()
left join pk_fastgroupbuy f on gb.fastgroupbuyid=f.id
where gb.id in (".$arr_str.") and (gb.status="2" or gb.status="3")
and gb.endtime>UNIX_TIMESTAMP() and gb.starttime group by onid limit $start,$num"; return TableSystem::query($sql); 变量说明:$arr_str是一个由pk_groupbuy中主键 id组成的一个数组,经过explode函数得到的字符串, $start,$num分别是查询的开始记录数,和要查询的记录数。 问题说明:pk_ontop表中ontopid在不能记录中有重复现象 比如:我只需要查询出来pk_ontop中当天置顶的ontopid,即商品id,不需要其他的商品信息,查询出来的有ontopid就算有重复现象,这时我可以通过去除数组重复元素解决问题,但是如果我要查询出相应商品id并查询其他相关联表中的信息,并按照ontop表中starttime,status,paixu字段进行排序等操作时,就需要join pk_ontop表,所以之前解决的重复问题就又会出现,无法处理,特别是在api中,是不允许出现重复的,这要怎么办呢?我也不会额,别人教我这样弄,请大家参考下: $sql = "SELECT DISTINCT(ontopid),starttime,paixu FROM pk_ontop ORDER BY starttime DESC,STATUS ASC,paixu ASC LIMIT 17"; $sql = "select gb.local,f.phone,f.shopname as fshopname,gb.maxnum,gb.intro,gb.buynum,g.pic,f.googleaddress,gb.goodsclassid,gb.sellerid,f.img,gb.province,gb.city,gb.id,gb.title,g.pic,gb.starttime, 解决方案_解决当distinct和join同时存在distinct失效问题 http://m.bbyears.com/zhufuduanxin/30703.html
$arr = TableSystem::query($sql);
foreach($arr as $key=>$val){
$topids[$key] = $val["ontopid"];
}
$arr_str = implode(",",$topids);
$arr1 = TableSystem::query($sql);
gb.endtime,gb.price,gb.goodsprice from pk_groupbuy gb
left join pk_goods g on gb.goodsid=g.id and g.status=2 and g.invalid > UNIX_TIMESTAMP()
left JOIN pk_fastgroupbuy f ON f.id=gb.fastgroupbuyid
where (gb.status="2" or gb.status="3") and gb.endtime > UNIX_TIMESTAMP()
and gb.starttime < UNIX_TIMESTAMP() AND gb.id in (".$arr_str.")";
$arr2 = TableSystem::query($sql);
foreach($arr2 as $key=>$val){
$local[$val["id"]] = $val["local"];
$phone[$val["id"]] = $val["phone"];
$fshopname[$val["id"]] = $val["fshopname"];
$maxnum[$val["id"]] = $val["maxnum"];
$intro[$val["id"]] = $val["intro"];
$buynums[$val["id"]] = $val["buynum"];
$fgoogleaddresss[$val["id"]] = $val["googleaddress"];
$goodsclassid[$val["id"]] = $val["goodsclassid"];
$sellids[$val["id"]] = $val["sellerid"];
$provices[$val["id"]] = $val["province"];
$citys[$val["id"]] = $val["city"];
$titles[$val["id"]]= $val["title"];
$pics[$val["id"]] = $val["pic"] ? $val["pic"] : $val["img"];
$starttimes[$val["id"]] = $val["starttime"];
$endtimes[$val["id"]] = $val["endtime"];
$prices[$val["id"]] = $val["price"];
$goodsprices[$val["id"]] = $val["goodsprice"];
}
unset($arr2);
foreach($arr1 as $key=>$val){
$list[$key]["id"] = $val["ontopid"];
$list[$key]["province"] = $provices[$val["ontopid"]];
$list[$key]["city"] = $citys[$val["ontopid"]];
$list[$key]["title"] = $titles[$val["ontopid"]];
$list[$key]["pic"] = $pics[$val["ontopid"]];
$list[$key]["starttime"] = $starttimes[$val["ontopid"]];
$list[$key]["endtime"] = $endtimes[$val["ontopid"]];
$list[$key]["price"] = $prices[$val["ontopid"]];
$list[$key]["goodsprice"] = $goodsprices[$val["ontopid"]];
$list[$key]["sellerid"] = $sellids[$val["ontopid"]];
$list[$key]["fgoogleaddress"] = $fgoogleaddresss[$val["ontopid"]];
$list[$key]["goodsclassid"] = $goodsclassid[$val["ontopid"]];
$list[$key]["buynum"] = $buynums[$val["ontopid"]];
$list[$key]["intro"] = $intro[$val["ontopid"]];
$list[$key]["maxnum"] = $maxnum[$val["ontopid"]];
$list[$key]["fshopname"] = $fshopname[$val["ontopid"]];
$list[$key]["fphone"] = $phone[$val["ontopid"]];
$list[$key]["local"] = $local[$val["ontopid"]];
}
return $list;