mysql GROUP_CONCAT 聚合函数

作用

左连接的时候，一对多，然后子表的多个值作为一个值。

应用场景

查询列表的时候，经常会用到。特别是左连接的表比较多的时候。

示例

SELECT GROUP_CONCAT(column_name SEPARATOR 'separator') FROM table_name GROUP BY column_name;

完整sql

SELECT
  a.*,
  t2.*,
  t3.*,
  GROUP_CONCAT(
    CONCAT(
      CONCAT(
        SUBSTRING(t4.night_start_time, 1, 2),
        ':',
        SUBSTRING(t4.night_start_time, 3, 2)
      ),
      ' - ',
      CONCAT(
        SUBSTRING(t4.night_end_time, 1, 2),
        ':',
        SUBSTRING(t4.night_end_time, 3, 2)
      ),
      ' ',
      CONCAT(t4.night_fee, '元')
    ) SEPARATOR '\n'
  ) AS night_service_details
FROM
  steward_shop a
  LEFT JOIN `steward_app_com` t2 ON t2.steward_com_id = a.steward_com_id
  LEFT JOIN `steward_app_com_ota` t3 ON t3.steward_com_id = a.steward_com_id
  LEFT JOIN `steward_shop_night_fee` t4 ON t4.steward_shop_id = a.steward_shop_id
GROUP BY
  a.steward_shop_id
ORDER BY
  a.update_time DESC
limit
  0, 10