如何使用find_in_set计算值？

发布于 2024-11-09 11:08:47 字数 218 浏览 1 评论 0原文

在我的数据库中，uid 是自动增量值，表示用户 id，u_follow 显示跟随其他用户的用户，并用其 uid 分隔逗号。我想计算每个用户有多少关注者。我怎样才能做到这一点？

uid        u_follow
1          2,3
2          1,3
3          1,2
4          NULL
5          2,3,4

原文

In my db, uid is the autoincrement value indicates user ids and u_follow shows the user that follows other users with their uid seperated comma. i want to count that how many followers has each user. How can i do that ?

uid        u_follow
1          2,3
2          1,3
3          1,2
4          NULL
5          2,3,4

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

路还长，别太狂 2024-11-16 11:08:47

每列存储一个值，否则您将无法进行关系查询。有关数据库规范化的介绍，请参阅这篇精彩的讨论。

用户

uid
...

关注者

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

然后：

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

如果您想包含没有关注者的用户，请执行以下操作：

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

Store one value per column, otherwise you just can't do relational queries. See this excellent discussion for an introduction to 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 include users with no followers do something like:

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

回复收藏 0 原文

~没有更多了~