php - 如何用sql计算数组中的每个项目
问题描述
我有一个包含 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 分钟以上才能完成,有吗更好的方法来做到这一点?
解决方案
有一个更好的方法来做到这一点。您的表格需要标准化:
代替
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,那么您可以使用 alike '%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,%'
查找成员组 ID 为 1 的用户 ID。(它们将用逗号封装)现在,您可以手动构建新的规范化表,我将调用它user_member
。
插入 membergroupid 1:
insert into user_member (userid,membergroupid) select userid,'1' from your_table where temp like '%,1,%';
你可以制作一个循环遍历所有 membergroupid 的 php 脚本。
请记住,这like %...%
不是很有效,所以甚至不要考虑依靠它来进行计数。它会起作用,但它不可扩展。使用它来构建规范化表会更好。
推荐阅读
- android - 如何在本机反应中读取和捕获android数据库中的所有短信?
- azure-functions - 从公共 Azure 函数到 Azure PostgreSQL 的安全连接
- google-apps-script - 在满足“IF”条件后仅运行一次谷歌应用脚本,每月一次
- interface - Hyperledger Fabric:如何获取部署的链码中包含的所有函数的名称及其参数和返回类型
- java - Fetch.lazy 不适用于 spring boot jpa,一定是什么问题?
- php - 如何将字符串转换为日期数据类型
- python - Python:更优雅的断言函数输入类型的方法
- python - Django Rest - 创建和操作方法返回错误 403 禁止
- php - 如何在上传前检查文件的 upload_max_filesize 和 post_max_size?
- mysql - 限制 SELECT 中的序号