Geek asks and answers

How to count values ​​using find_in_set?

In my db, uid is the auto-increment value, indicates the user IDs, and u_follow shows the user who follows other users with their uid separated by comma. I want to calculate how many followers each user has. How can i do this?

uid        u_follow
1          2,3
2          1,3
3          1,2
4          NULL
5          2,3,4
+3
source share
1 answer

Save one value per column, otherwise you simply cannot perform relational queries. See this great discussion for a discussion of database normalization.

users

uid
...

followers

uid u_follow
1   2
1   3
2   1
2   3
3   1
3   2
5   2
5   3
5   4

Then:

select u_follow, count(*) as num_followers from followers group by u_follow

If you want to enable users without subscribers, follow these steps:

with a as (
  select u_follow, count(*) as num_followers
  from followers group by u_follow
)
select users.uid, coalesce(a.num_followers,0)
from users outer join a on users.uid = a.u_follow
+4
source

More articles:


All Articles