我有以下[表A]
id res1 res2
1 a f
1 b f
1 b f
1 c f
2 e g
2 e g
2 e g
2 f g
我在做了一个小组讨论后得到了以下信息
select
id,
group_concat(case when cnt = 1 then res1 else concat(cnt, ' ', res1) end) as r1,
group_concat(case when cnt = 1 then res2 else concat(cnt, ' ', res2) end) as r2
from
(
select id, res1,res2, count(*) as cnt
from [table a]
group by id, res1,res2
) t
group by id;
id r1 r2
1 a,2 b,c f,2 f,f
2 3 e,f 3 g,g
res1列正常,但res2列正在复制res1列。
基本上我想打印一个字符出现在该字符之前的次数的值。
。我想要以下格式。
id r1 r2
1 a,2 b,c 4 f
2 3 e,f 4 g
我怎样才能做到?
最佳答案
我的方法是对res1
和res2
列使用两个独立的子查询进行两次汇总/聚合。第一次聚合超过id
和res1
(或res2
),并获取每个字母或单词的计数。然后,再次聚合,这次只对id
进行聚合,以获得每个id
的逗号分隔字符串。最后,将这些子查询连接在一起以获得最终结果。
SELECT
t1.id, t1.r1, t2.r2
FROM
(
SELECT t.id, GROUP_CONCAT(res1agg ORDER BY res1) AS r1
FROM
(
SELECT
id,
res1,
CASE WHEN COUNT(*) = 1 THEN res1
ELSE CONCAT(CAST(COUNT(*) AS CHAR(50)), res1) END AS res1agg
FROM yourTable
GROUP BY id, res1
) t
GROUP BY t.id
) t1
INNER JOIN
(
SELECT t.id, GROUP_CONCAT(res2agg ORDER BY res2) AS r2
FROM
(
SELECT
id,
res2,
CASE WHEN COUNT(*) = 1 THEN res2
ELSE CONCAT(CAST(COUNT(*) AS CHAR(50)), res2) END AS res2agg
FROM yourTable
GROUP BY id, res2
) t
GROUP BY t.id
) t2
ON t1.id = t2.id;
输出:
演示:
Rextester
关于mysql - 修改MySql中通过group_Concat实现的值?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/43673256/