MySQL行转列（工作记录）

MySQL行转列

工作中遇到的问题，记录一下
原sql：

SELECT DISTINCT
	e.id id,
	e.NAME NAME,
	e.detail_address detailAddress,
	e.parking_lot parkingLot,
	e.parking_lot_gps parkingLotGps,
	e.contacts contacts,
	e.type type,
	t.id teamId,
	t.team_name teamName,
	t.begin_name beginName,
	t.end_name endName
FROM
	system_enterprise e
	LEFT JOIN ope_ent_link_team l ON e.id = l.ent_id
	LEFT JOIN ope_team t ON l.team_id = t.id
WHERE
	e.type = 'ENTERPRISE_LOGISCIS'

输出结果

修改后的sql：

SELECT
	e.id AS id,
	e.NAME AS NAME,
	e.detail_address AS detailAddress,
	e.parking_lot AS parkingLot,
	e.parking_lot_gps AS parkingLotGps,
	e.contacts AS contacts,
	e.type AS type,
	t.id AS teamId,
	t.team_name AS teamName,
	t.begin_name AS beginName,
	t.end_name AS endName,
	GROUP_CONCAT( DISTINCT c.car_type ORDER BY c.car_type ASC SEPARATOR ', ' ) AS carTypes 
FROM
	system_enterprise e
	LEFT JOIN ope_ent_link_team l ON e.id = l.ent_id
	LEFT JOIN ope_team t ON l.team_id = t.id
	LEFT JOIN fleet_car c ON e.id = c.ent_id 
WHERE
	e.type = 'ENTERPRISE_LOGISCIS' 
GROUP BY
	e.id

修改过程中遇到了一个错误信息

公司用的MySQL 8.0 的阿里的库
使用下列代码查看这段设置信息

SELECT @@GLOBAL.sql_mode;

主要的错误信息就是有 ：ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES这个，去除即可
去除语句

SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

当然，如果想要改回来，修改的语句也在这

SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

参考
大佬的代码