【MYSQL】解决当distinct和join同时存在distinct失效问题
⼀起来看看这个例⼦:
$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 pid=gb.id
left join pk_goods g dsid=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")
dtime>UNIX_TIMESTAMP() and gb.starttime<UNIX_TIMESTAMP()
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';
$arr = TableSystem::query($sql);
foreach($arr as $key=>$val){
$topids[$key] = $val['ontopid'];
}
$arr_str = implode(',',$topids);
$arr1 = TableSystem::query($sql);
distinct查询
$sql = 'select gb.local,f.phone,f.shopname as
fshopname,gb.maxnum,gb.intro,gb.buynum,g.dsclassid,gb.sellerid,f.img,gb.province,gb.city,gb.id,gb.tit      gb.endtime,gb.dsprice from pk_groupbuy gb
left join  pk_goods g dsid=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") dtime > 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'];

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。