mysql group_concat里面有一个计数? [英] mysql group_concat with a count inside?
本文介绍了mysql group_concat里面有一个计数?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有2个表,用户和关注者.下表后面有一个名为状态的列.我想根据状态对每个用户进行分组的次数进行计数.
i have 2 tables, users and follows. table follows has a column named status. I would like to count how many follows each user has grouping by the status.
下面的查询返回每个用户的每种状态类型的记录.
The query below returns a record for each status type for each user.
SELECT users.name as user_name, f.status, count(f.id)
FROM users
JOIN application_follows f ON f.user_id = users.id
GROUP BY users.id, f.status
ORDER BY users.id
返回类似:
user_name status count
mike new 10
mike old 5
tom new 8
tom old 9
但是我想要更友好的东西:
but i would like something more friendly like:
user_name status_count
mike new,10|old,5
tom new,8|old,9
使用group_concat进行了尝试并计数,但没有成功.有任何线索吗?
tried using group_concat and count but didnt work. Any clues?
推荐答案
您需要使用GROUP BY两次,首先从下面的(user_id,status)开始获取计数,然后在user_id上从连接表到concat:
You need to use GROUP BY twice, first on (user_id, status) from follows to get counts then on user_id from joined table to concat:
SELECT users.name, GROUP_CONCAT( CONCAT(f.status, ',', f.cnt) SEPARATOR '|' )
FROM users
JOIN
( SELECT user_id, status, count(id) AS cnt
FROM application_follows
GROUP BY user_id, status ) f
ON f.user_id = users.id
GROUP BY users.id
这篇关于mysql group_concat里面有一个计数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文