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_