解决当distinct和join同时存在distinct失效问题

解决当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 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<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中,是不允许出现重复的,这要怎么办呢?我也不会额,别人教我这样弄,请大家参考下:
  1. $sql = 'SELECT DISTINCT(ontopid),starttime,paixu FROM pk_ontop ORDER BY starttime DESC,STATUS ASC,paixu ASC LIMIT 17';
  2.     $arr = TableSystem::query($sql);
  3.     foreach($arr as $key=>$val){
  4.      $topids[$key] = $val['ontopid'];
  5.     }
  6.     $arr_str = implode(',',$topids);
  7.     $arr1 = TableSystem::query($sql);

  8.     $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,
  9.       gb.endtime,gb.price,gb.goodsprice from pk_groupbuy gb
  10.       left join  pk_goods g on gb.goodsid=g.id and g.status=2 and g.invalid > UNIX_TIMESTAMP()
  11.       left JOIN  pk_fastgroupbuy f ON f.id=gb.fastgroupbuyid
  12.       where (gb.status="2" or gb.status="3") and gb.endtime > UNIX_TIMESTAMP()
  13.       and gb.starttime < UNIX_TIMESTAMP()  AND gb.id in ('.$arr_str.')';
  14.    
  15.     $arr2 = TableSystem::query($sql);
  16.     foreach($arr2 as $key=>$val){
  17.      $local[$val['id']] = $val['local'];
  18.      $phone[$val['id']] = $val['phone'];
  19.      $fshopname[$val['id']] = $val['fshopname'];
  20.      $maxnum[$val['id']] = $val['maxnum'];
  21.      $intro[$val['id']] = $val['intro'];
  22.      $buynums[$val['id']] = $val['buynum'];
  23.      $fgoogleaddresss[$val['id']] = $val['googleaddress'];
  24.      $goodsclassid[$val['id']] = $val['goodsclassid'];
  25.      $sellids[$val['id']] = $val['sellerid'];
  26.      $provices[$val['id']] = $val['province'];
  27.      $citys[$val['id']] = $val['city'];
  28.      $titles[$val['id']]= $val['title'];
  29.      $pics[$val['id']] = $val['pic'] ? $val['pic'] : $val['img'];
  30.      $starttimes[$val['id']] = $val['starttime'];
  31.      $endtimes[$val['id']] = $val['endtime'];
  32.      $prices[$val['id']] = $val['price'];
  33.      $goodsprices[$val['id']] = $val['goodsprice'];
  34.     }
  35.     unset($arr2);
  36.     foreach($arr1 as $key=>$val){
  37.      $list[$key]['id'] = $val['ontopid'];
  38.      $list[$key]['province'] = $provices[$val['ontopid']];
  39.      $list[$key]['city'] = $citys[$val['ontopid']];
  40.      $list[$key]['title'] = $titles[$val['ontopid']];
  41.      $list[$key]['pic'] = $pics[$val['ontopid']];
  42.      $list[$key]['starttime'] = $starttimes[$val['ontopid']];
  43.      $list[$key]['endtime'] = $endtimes[$val['ontopid']];
  44.      $list[$key]['price'] = $prices[$val['ontopid']];
  45.      $list[$key]['goodsprice'] = $goodsprices[$val['ontopid']];
  46.      $list[$key]['sellerid'] = $sellids[$val['ontopid']];
  47.      $list[$key]['fgoogleaddress'] = $fgoogleaddresss[$val['ontopid']];
  48.      $list[$key]['goodsclassid'] = $goodsclassid[$val['ontopid']];
  49.      $list[$key]['buynum'] = $buynums[$val['ontopid']];
  50.      $list[$key]['intro'] = $intro[$val['ontopid']];
  51.      $list[$key]['maxnum'] = $maxnum[$val['ontopid']];
  52.      $list[$key]['fshopname'] = $fshopname[$val['ontopid']];
  53.      $list[$key]['fphone'] = $phone[$val['ontopid']];
  54.      $list[$key]['local'] = $local[$val['ontopid']];
  55.     }
  56.     return $list;
复制代码

作者: feiyang10086   发布时间: 2011-06-08

回复 feiyang10086


不给表结构无从知道是怎么回事。

作者: 106033177   发布时间: 2011-06-08