oracle去重复记录的方式不用distinct而用groupby

http://chenxy.blog.51cto.com/729966/728112,

 

参考文档二：http://topic.csdn.net/u/20080508/10/4b56808e-ab77-4d6e-8e75-47f281e8e33c.html

 

例如下面的sql语句：

单个字段去掉重复：

select pp.p_product_id,
       pp.product_name,
       pp.product_level_name,
       ud.depart_path_name,
       pp.recommand_level,
       pp.product_type,
       pp.travelline_type,
       pp.departure,
       pp.destination,
       pp.sale_city_codes,
       pp.travel_day_span,
       pp.valid,
       pp.modify_datetime
  from p_product pp ,u_department ud where pp.u_department_id=ud.department_id  
  and pp.u_department_id in(select us.u_department_id from p_product us group by us.u_department_id having count(*)>1)
  order by pp.modify_datetime desc;

多个字段去掉重复：

select pp.p_product_id,
       pp.product_name,
       pp.product_level_name,
       ud.depart_path_name,
       pp.recommand_level,
       pp.product_type,
       pp.travelline_type,
       pp.departure,
       pp.destination,
       pp.sale_city_codes,
       pp.travel_day_span,
       pp.valid,
       pp.modify_datetime
  from p_product pp ,u_department ud where pp.u_department_id=ud.department_id  
  and (pp.u_department_id,ud.depart_path_name)in(select us.u_department_id,ud.depart_path_name from p_product us,u_department ud group by us.u_department_id,ud.depart_path_name having count(*)>1)
  order by pp.modify_datetime desc;

 

 

最好不使用disctinct