php - sql如何统计数组中的每一项

Question

我有一个包含 membergroupids 的用户表，用户表如下所示:

userid   membergroupids
1        1,2
2        2,3
3        2,3,4

我想用sql输出这样的结果

membergroupid    count
1                1
2                3
3                2
4                1

我尝试使用 SELECT membergroupids FROM user，然后使用 php 循环遍历结果并获取计数，但它适用于一小部分用户表，但我有一个非常大的用户表，选择查询本身需要 1 分钟以上才能完成，有更好的方法吗？

Answer 1

有一种好得多的方法来做到这一点。您的表格需要规范化:

代替

userid   membergroupids
1        1,2
2        2,3
3        2,3,4

应该是

userid   membergroupids
1        1
1        2
2        2
2        3
3        2
3        3
3        4

从这里开始，这是一个获取计数的简单查询(假设此表名为 your_table:

select count(membergroupids) as numberofgroups, userid 
from your_table 
group by userid 
order by userid

那么，真正的问题是让您的表格规范化。如果您只有 9 个 membergroupid，那么您可以使用 like '%1%' 来查找 membergroupid #1 的所有用户 ID。但是如果你有 10，那么它就无法区分 1 和 10。遗憾的是，你不能指望逗号来帮助你区分，因为数字可能没有被逗号包围。

除非……

使用逗号封装的组 ID 创建新字段

您可以创建一个新字段并使用 membergroupids 填充它，并使用 concat 将其用逗号括起来(查看您的数据库文档)。沿着这条线的东西:

update your_table set temp=concat(',', membergroupids, ',');

这可以为您提供如下表结构:

userid   membergroupids  temp
1        1,2             ,1,2,
2        2,3             ,2,3,
3        2,3,4           ,2,3,4,

现在，您可以在新字段中获取不同的成员组 ID，即 where temp like '%,1,%' 以查找 membergroupid 为 1 的用户 ID。(它们将是用逗号封装)现在，您可以手动构建新的规范化表，我将其称为 user_member。

插入membergroupid 1:

insert into user_member (userid,membergroupid) select userid,'1' from your_table where temp like '%,1,%';

您可以制作一个循环遍历所有成员组 ID 的 php 脚本。

请记住，like %...% 效率不高，因此甚至不要考虑依靠它来进行计数。它会工作，但它不可扩展。用它来构建规范化表会好得多。