mysql - SQL:从多个表中选择

Question

我有 10 个表，它们之间通过父键进行通信，我正在尝试提取我需要的所有信息。

这是我现在正在使用的查询:

SELECT a.date_in, a.date_out, b.name, b.phone, b.birthdate,
b.country, b.hotel, b.room_nr, b.passport_nr, c.email,
d.size, e.name, GROUP_CONCAT(g.service), GROUP_CONCAT(h.service)
, GROUP_CONCAT(i.time), GROUP_CONCAT(j.location)
FROM reservation a, rider b, user c,
bike_size d, bike e, services_reservation f, services g,
bike_shipping h, bike_shipping_reservation i
, bike_shipping_location j WHERE a.rider_id = b.id
AND b.user_id = c.id AND a.bike_size_id = d.id AND
d.bike_id = e.id AND a.id = f.reservation_id AND
f.services_id = g.id
AND h.id = i.bike_shipping_id AND a.id = i.reservation_id
AND i.bike_shipping_location_id = j.id
AND a.id = 80 

问题是我使用 GROUP_CONCAT 函数的所有列都会重复多次。例如:

GROUP_CONCAT(g.service) column
Road SPD,Road SPD,Bike Helmet,Bike Helmet,TT Bar,TT Bar,Garmin Edge 
810,Garmin Edge 810

应该是

Road SPD,Bike Helmet,TT Bar,Garmin Edge 810

有什么办法可以解决这个问题吗？我的数据库方案好不好？

SQLFiddle: http://sqlfiddle.com/#!9/8bc033/33

Answer 1

在 GROUP_CONCAT 语句中使用 DISTINCT

SELECT a.date_in, a.date_out, b.name, b.phone, b.birthdate,
b.country, b.hotel, b.room_nr, b.passport_nr, c.email,
d.size, e.name, GROUP_CONCAT(DISTINCT g.service), GROUP_CONCAT(DISTINCT h.service)
, GROUP_CONCAT(DISTINCT i.time), GROUP_CONCAT(DISTINCT j.location)
FROM reservation a, rider b, user c,
bike_size d, bike e, services_reservation f, services g,
bike_shipping h, bike_shipping_reservation i
, bike_shipping_location j WHERE a.rider_id = b.id
AND b.user_id = c.id AND a.bike_size_id = d.id AND
d.bike_id = e.id AND a.id = f.reservation_id AND
f.services_id = g.id
AND h.id = i.bike_shipping_id AND a.id = i.reservation_id
AND i.bike_shipping_location_id = j.id
AND a.id = 80