Optimize SQL query by eliminating extensive GROUP BY-CodePudding

I have a query with multiple select and a single aggregated value, coming from a joined table, resulting an extensive and ugly GROUP BY (because of the one-to-many relation with the joined table). It's something like this:

SELECT user.id, user.name, user.type, etc.
GROUP_CONCAT(car.id SEPARATOR ', ') AS cars
FROM user
INNER JOIN car ON user.id = car.userid
GROUP BY user.id, etc.
ORDER BY user.name, user.type, cars

I would like to eliminate the long GROUP BY, but how could I get the aggregated value without the JOIN? Is there a way with something like a subquery to join the values together like with the GROUP_CONCAT?

CodePudding user response：

You can aggregate in car and then join to user:

SELECT u.id, u.name, u.type, etc.,
       c.cars
FROM user u
INNER JOIN (
  SELECT userid, GROUP_CONCAT(id SEPARATOR ', ') AS cars 
  FROM car 
  GROUP BY userid
) c ON u.id = c.userid
ORDER BY u.name, u.type, c.cars;

Or with a correlated subquery, which is equivalent to a LEFT join but may perform better:

SELECT u.id, u.name, u.type, etc.,
       (SELECT GROUP_CONCAT(c.id SEPARATOR ', ') FROM car c WHERE u.id = c.userid) AS cars 
FROM user u
ORDER BY u.name, u.type, cars;

CodePudding user response：

You can group this way

SELECT user.id, user.name, user.type,
       uc.cars
FROM (
   SELECT userid, GROUP_CONCAT(id SEPARATOR ', ') AS cars
   FROM car
   GROUP BY userid) uc
INNER JOIN user ON user.id = uc.userid