场景:使用左连接关联查询时,发现查询的结果中有两条一模一样的数据,因此,需要对查询结果去重。
解决方案:在具有唯一索引的字段前加上DISTINCT关键字即可。
在加关键字之前的SQL:
SELECT
a.order_id,a.user_id,a.biz_price,a.start_effect,a.end_effect,a.order_type, a.pay_time, a.write_off_lastdate,
a.write_off_status, b.store_img, b.store_name, a.order_status, a.pay_status,c.type_name,d.biz_name,cp.coupon_name
FROM
tb_service_order a
LEFT JOIN tb_service_store b ON a.store_code = b.store_code
LEFT JOIN tb_coupon_type c ON c.id = a.order_type
LEFT JOIN tb_service_merchant_biz d ON d.biz_code = a.biz_code
LEFT JOIN tb_coupon cp ON cp.coupon_type_id = c.id
WHERE
a.user_id = 7860
查询结果:
加过关键字之后的SQL:
SELECT
DISTINCT a.order_id,a.user_id,a.biz_price,a.start_effect,a.end_effect,a.order_type, a.pay_time, a.write_off_lastdate,
a.write_off_status, b.store_img, b.store_name, a.order_status, a.pay_status,c.type_name,d.biz_name,cp.coupon_name
FROM
tb_service_order a
LEFT JOIN tb_service_store b ON a.store_code = b.store_code
LEFT JOIN tb_coupon_type c ON c.id = a.order_type
LEFT JOIN tb_service_merchant_biz d ON d.biz_code = a.biz_code
LEFT JOIN tb_coupon cp ON cp.coupon_type_id = c.id
WHERE
a.user_id = 7860
查询结果: