mysql行转列聚合一对多的结果集|GROUP_CONCAT函数的使用|TRIM()函数的使用

1.行转列的结果集

select
			distinct tari.id,
			tari.link as link3,
			(case
				when tac.status = 4 then ba.create_time
				else null
			end ) as secondEditTime,
			(case
				when tac.status = 1 then ba.link
				else null
			end) as link1,
			(case
				when tac.status = 2 then ba.link
				else null
			end) as link2,
			(case
				when tac.status = 4 then ba.link
				else null
			end) as link4
		from
			table1 tari
		inner join table2 tac on
			tari.id = tac.business_id
		left join table3 ba on
			ba.id = tac.attach_id

  表名为敏感信息，我改成了table1 、table2的形式。无需知道表的具体结构，只需要知道table1:table2:table3的关系为1：n：n就行了，即一对多关系。
  上面的sql结果为
  id是table1的唯一主键，因为表是一对多的关系，所以行转列之后出现这种情况。很显然这不是我们想要的结果集，需要把重复的id内容聚合起来。

---

2.GROUP_CONCAT函数的使用

select
		t.id,
		t.link3 as link3,
		TRIM(both ',' from GROUP_CONCAT(IFNULL(secondEditTime, '') separator ',')) as secondEditTime,
		TRIM(both ',' from GROUP_CONCAT(IFNULL(link1, '') separator ',')) as l1,
		TRIM(both ',' from GROUP_CONCAT(IFNULL(link2, '') separator ',')) as l2,
		TRIM(both ',' from GROUP_CONCAT(IFNULL(link4, '') separator ',')) as l4
	from
		(
		select
			distinct tari.id,
			tari.link as link3,
			(case
				when tac.status = 4 then ba.create_time
				else null
			end ) as secondEditTime,
			(case
				when tac.status = 1 then ba.link
				else null
			end) as link1,
			(case
				when tac.status = 2 then ba.link
				else null
			end) as link2,
			(case
				when tac.status = 4 then ba.link
				else null
			end) as link4
		from
			tbo_audit_report_info tari
		inner join tbo_attach_correlation tac on
			tari.id = tac.business_id
		left join blade_attach ba on
			ba.id = tac.attach_id
    ) t
	group by
		t.id

上面的sql结果为

  GROUP_CONCAT函数是一个聚合函数，用于将多个行中的值连接成一个单独的字符串。具体来说，GROUP_CONCAT函数会将同一组内的多个值连接起来，形成一个由逗号分隔的字符串。多用于一对多和多对多的连表查询。
  举个更简单清晰的例子
  现在有以下表和数据

CREATE TABLE family_activities (
    family_member VARCHAR(100),
    activity VARCHAR(100)
);
INSERT INTO family_activities (family_member, activity)
VALUES ('张三', '篮球'),
       ('张三', '游泳'),
       ('李四', '烹饪'),
       ('李四', '音乐'),
       ('王五', '舞蹈');

执行

SELECT family_member, GROUP_CONCAT(activity SEPARATOR ', ') AS activity_list
FROM family_activities
GROUP BY family_member;

之后的结果是

3.TRIM()函数的使用

但是实际操作中，行转列的结果集里面会有很多的null和空串
你会得到类似 ,a,b,c或者a,b,c,的情况，所以需要TRIM函数来把逗号掐头去尾。

TRIM(str)：去除字符串str两端的空格
TRIM(LEADING str FROM str)：去除字符串str开头的str字符
TRIM(TRAILING str FROM str)：去除字符串str结尾的str字符
TRIM(BOTH str FROM str)：去除字符串str两端的str字符

比如TRIM(BOTH 'hw' FROM 'hello world') 会返回’ell ol’，去除了字符串两端的’h’和’w’字符