当前位置： 移动技术网 > IT编程>数据库>Mysql > mysql使用group_concat()列转行后去重

mysql使用group_concat()列转行后去重

2020年07月14日  | 移动技术网IT编程  | 我要评论

未去重sql

SELECT
	a.start_time AS "startTime",
	a.end_time AS "endTime",
	a.title AS "title",
	a.content AS "content",
	a.meetingroom_id AS "meetingroomId",
	a.contact AS "contact",
	a.matters AS "matters",
	a.mark AS "mark",
	a.id AS "id",
	a.created_time AS "createdTime",
	a.processInstance_id AS "processInstanceId",
	a.meetingtype AS "meetingtype",
	a.meetinghost AS "meetinghost",
	a.meetingmethod AS "meetingmethod",
	b.ASSIGNEE_ AS "ASSIGNEE",
	a.STATUS AS "status",-- 发布后的状态
	d.NAME AS "meetingRoom.name",
	d.pic_url AS "meetingRoom.picUrl",
	GROUP_CONCAT( n.title ) AS "threeoneModelNames" 
FROM
	act_hi_taskinst b
	INNER JOIN rz_meeting a ON a.processinstance_id = b.PROC_INST_ID_
	LEFT JOIN rz_meetingroom d ON a.meetingroom_id = d.id
	LEFT JOIN rz_threeone_model n ON FIND_IN_SET( n.id, a.threeone_model_ids ) 
WHERE
	b.ASSIGNEE_ = '60b34bc00a3e4df9afd37e2384755719' 
	AND b.END_TIME_ IS NOT NULL 
GROUP BY
	a.id 
ORDER BY
	a.created_time DESC

去重sql（只需要在GROUP_CONCAT函数里面添加distinct）

SELECT
a.start_time AS "startTime",
a.end_time AS "endTime",
a.title AS "title",
a.content AS "content",
a.meetingroom_id AS "meetingroomId",
a.contact AS "contact",
a.matters AS "matters",
a.mark AS "mark",
a.id AS "id",
a.created_time AS "createdTime",
a.processInstance_id AS "processInstanceId",
a.meetingtype AS "meetingtype",
a.meetinghost AS "meetinghost",
a.meetingmethod AS "meetingmethod",
b.ASSIGNEE_ AS "ASSIGNEE",
a.STATUS AS "status",-- 发布后的状态
d.NAME AS "meetingRoom.name",
d.pic_url AS "meetingRoom.picUrl",
GROUP_CONCAT(distinct  n.title ) AS "threeoneModelNames" 
FROM
act_hi_taskinst b
INNER JOIN rz_meeting a ON a.processinstance_id = b.PROC_INST_ID_
LEFT JOIN rz_meetingroom d ON a.meetingroom_id = d.id
LEFT JOIN rz_threeone_model n ON FIND_IN_SET( n.id, a.threeone_model_ids ) WHERE
b.ASSIGNEE_ = '60b34bc00a3e4df9afd37e2384755719' 
AND b.END_TIME_ IS NOT NULL 
GROUP BY
a.id 
ORDER BY
a.created_time DESC

只需要在GROUP_CONCAT函数里面添加distinct

本文地址：https://blog.csdn.net/qq_28322319/article/details/107290039

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网