mysql 的 group_concat 函数（连接字段）

mysql 的 group_concat 函数（连接字段）

完整的语法如下：

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])

1、以id分组，把name字段的值打印在一行，逗号分隔(默认)

select id,group_concat(name) from aa group by id;

以id分组，把name字段的值打印在一行，分号分隔

select id,group_concat(name separator ‘;’) from aa group by id;

示例：

SELECT
	a.ID_ AS ID_,
	a.NAME_ AS NAME_,
	b.NAME_ AS BRAND_,
	a.SOURCE_TYPE_ AS SOURCE_TYPE_,
	c.NAME_ AS SOURCE_TYPE_ID_,
	a.SOURCE_NAME_ AS SOURCE_NAME_,
	GROUP_CONCAT(distinct(e.NAME_))AS SAIE_MANAGER_,
	a.CONTRACT_CODE_ AS CONTRACT_CODE_,
	f.DISTRIBUTION_MONEY_ AS CONTRACT_MONEY_,
	a.DESIGN_MONEY_ AS DESIGN_MONEY_
FROM
	(
		SELECT
			a.ID_ AS ID_,
			a.NAME_ AS NAME_,
			a.BRAND_ AS BRAND_,
			a.SOURCE_TYPE_ AS SOURCE_TYPE_,
			a.SOURCE_NAME_ AS SOURCE_NAME_,
			a.CONTRACT_CODE_ AS CONTRACT_CODE_,
			a.DESIGN_MONEY_ AS DESIGN_MONEY_
		FROM
			t_project_project a
		WHERE
			a.PROJECT_TYPE_ = 9700524
		AND a.DESIGN_MONEY_ IS NOT NULL
		AND a.DESIGN_MONEY_ != 0
		AND a.STATUS_ > 0
	) a
LEFT JOIN t_comm_dictionary b ON a.BRAND_ = b.ID_
LEFT JOIN t_comm_dictionary c ON a.SOURCE_TYPE_ = c.ID_
LEFT JOIN t_project_manager d ON a.ID_ = d.PROJECT_
LEFT JOIN t_comm_user e ON d.MANAGER_ = e.ID_
LEFT JOIN t_import_contract_detail f ON a.CONTRACT_CODE_ = f.CONTRACT_CODE_
GROUP BY a.ID_ ORDER BY a.CONTRACT_CODE_