Solve the problem of distinct failure when distinct and join exist at the same time

Solve the problem of distinct failure when distinct and join exist at the same time_PHP tutorial

WBOY

Release： 2016-07-13 17:05:53

Original

1315 people have browsed it

$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);

Variable description: $arr_str is an array composed of the primary key id in pk_groupbuy, a string obtained through the explode function,

$ Start, $ Num is the number of start records of the query, and the number of records to be queried.

Problem description: Ontopid in the pk_ontop table cannot be recorded and there is a duplicate phenomenon

For example: I only need to query the top ontopid of the day in pk_ontop, that is, the product ID, and do not need other product information. If the ontopid found in the query is duplicated, I can solve the problem by removing the repeated elements of the array. But if I want to query the corresponding product ID and query the information in other related tables, and sort according to the starttime, status, paixu fields in the ontop table, etc., I need to join the pk_ontop table, so the duplicate problems solved before will be solved again. It will appear and cannot be processed, especially in the API, duplication is not allowed. What should I do? I don’t know how to do it. Someone else taught me how to do it. Please refer to this:

$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);

    $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,
      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;