从2个不同的表中求和 [英] Adding sum from 2 different tables

问题描述

我有这样的东西 2张桌子: 影片 成员

I have something like this 2 tables: videos members

在成员表中，我有每个成员的名称:

In the members table I have the name of each member:

1 Tom
2 Bob
3 Zack
4 Dan
5 Casey

在视频表中，我有一列名为members的列，并且其中的名称以逗号分隔.

In the videos table I have a column named members and I have the names in there seperated by commas

1. Tom,Dan
2. Casey,Zack,Bob
3. Tom,Casey,Dan,Zack
4. Zack,Bob,Dan

我正在尝试显示每个成员出现多少次才能获得这些结果:

I'm trying to display how many times each member appears to get these results:

1 Tom = 2
2 Bob = 2
3 Zack = 3
4 Dan = 2
5 Casey = 2

我需要在SELECT SUM(members WHERE)处执行类似操作并使用LIKE吗?

Do I need to do something like SELECT SUM(members) WHERE and use LIKE?

推荐答案

我强烈建议您像其他人一样建议normalize您的数据.

根据您当前的设计，您可以使用

Based on your current design you can use FIND_IN_SET to accomplish the result you want.

SELECT 
 M.id,
 M.name,
 COUNT(*) total
FROM members M 
INNER JOIN videos V ON FIND_IN_SET(M.name,V.members) > 0
GROUP BY M.name
ORDER BY M.id

> 请参见演示

在给定的数据集上运行此查询，您将获得如下输出:

Running this query on your given data set you will get output like below:

| id |  name | total |
|----|-------|-------|
|  1 |   Tom |     2 |
|  2 |   Bob |     2 |
|  3 |  Zack |     3 |
|  4 |   Dan |     3 |
|  5 | Casey |     2 |

---

A必须阅读

---

A must read

在数据库列中存储分隔列表真的很糟糕吗?

更多

如果您对数据进行规范化，这就是您的vidoes表的外观:

This is how your vidoes table would look like if you normalize your data:

videos

videos

id   member_id

这篇关于从2个不同的表中求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！