在同一列中计算多个项目，从两个表中提取数据——mysql

I have 2 tables and I need to count the number of times any role shows up in each movie

我有两个表，我需要计算每一部电影中出现的角色的次数。

Example of Tables:

表的例子:

Movie Table

电影表

TITLE           MOVIEID     OTHER STUFF....
-------------   -------
MOVIE TITLE 1   ID# 1
MOVIE TITLE 2   ID# 2
MOVIE TITLE 3   ID# 3
ETC...

Role Table

角色表

ROLEID      MOVIEID     OTHER STUFF....
-------     -------
ROLE #1     ID# 1
ROLE #2     ID# 1
ROLE #3     ID# 2
ROLE #4     ID# 2
ROLE #5     ID# 3
ROLE #6     ID# 4
ROLE #7     ID# 5
ROLE #8     ID# 3
ETC....

I would like to get an output that looks like this:

我想得到这样的输出:

TITLE           NUMROLES
-------------   --------------------------------------
MOVIE TITLE 1   COUNT (ROLE.MOVIEID) FOR MOVIE TITLE 1
MOVIE TITLE 2   COUNT (ROLE.MOVIEID) FOR MOVIE TITLE 2
MOVIE TITLE 3   COUNT (ROLE.MOVIEID) FOR MOVIE TITLE 3
MOVIE TITLE 4   COUNT (ROLE.MOVIEID) FOR MOVIE TITLE 4
MOVIE TITLE 5   COUNT (ROLE.MOVIEID) FOR MOVIE TITLE 5
ETC...

I have tried a several different things but I seem to always get a total count of all the roles instead of them being counted individually.

我尝试过一些不同的东西，但我似乎总是得到所有角色的总数，而不是它们被单独计算。

My "best" output Example:

我的“最好”的输出示例:

mysql> SELECT TITLE, COUNT(ROLE.MOVIEID) AS NUMROLES
    -> FROM MOVIE, ROLE
    -> GROUP BY TITLE;
+-----------------------------+----------+
| TITLE                       | NUMROLES |
+-----------------------------+----------+
| Amadeus                     |      138 |
| Apollo 13                   |      138 |
| Batman                      |      138 |
| Batman & Robin              |      138 |
| Batman Begins               |      138 |
| Batman Forever              |      138 |
| Batman Returns              |      138 |
| Casablanca                  |      138 |
| Dirty Harry                 |      138 |
| Few Good Men, A             |      138 |
| Field of Dreams             |      138 |
| Fly, The                    |      138 |
| Forrest Gump                |      138 |
| Godfather, The              |      138 |
| Gone with the Wind          |      138 |
| Jerry Maguire               |      138 |
| Mrs. Doubtfire              |      138 |
| Naked City, The             |      138 |
| Sixth Sense, The            |      138 |
| Sudden Impact               |      138 |
| Terminator 2:  Judgment Day |      138 |
| Terminator, The             |      138 |
| Tootsie                     |      138 |
| Wizard of Oz, The           |      138 |
+-----------------------------+----------+
24 rows in set (0.02 sec)

I am thinking I don't have the proper knowledge of what I should be using in this case, can anyone point me in the right direction without just giving me the answer??

我在想我不知道在这种情况下我应该用什么，谁能在不给我答案的情况下指出正确的方向?

Bonus:

奖金:

I also need to filter out any titles that don't have at least 6 for count of (ROLE.MOVIEID) - I am thinking I need to use HAVING for that.

我还需要过滤掉任何数不超过6的标题(ROLE.MOVIEID)——我想我需要用have来做这个。

2 个解决方案

#1

You're really close, just need to tweak your JOIN (it is missing a predicate), and add your HAVING for your bonus:

你真的很接近了，只需要调整你的连接(它缺少一个谓词)，并加上你的奖金:

SELECT TITLE, COUNT(*) AS NUMROLES
FROM MOVIE M
JOIN ROLE R ON M.MOVIEID = R.MOVIEID --This is the join predicate (condition)
GROUP BY TITLE
HAVING COUNT(*) > 5;

This is explicit JOIN syntax. It's best practice to use this as the implicit variant (ie. commas in your FROM clause) is long since depreciated and harder to use anyway.

这是显式连接语法。最好的做法是把它用作隐式变体。你的FROM子句中的逗号)早就贬值了，无论如何也很难使用。

Note that you can use COUNT(r.movieID) if you want, but COUNT(*) will count the rows in the same fashion.

注意，如果需要，可以使用COUNT(r.movieID)，但COUNT(*)将以相同的方式对行进行计数。

#2

Your Query:

你的查询:

SELECT TITLE, COUNT(ROLE.MOVIEID) AS NUMROLES
FROM MOVIE, ROLE
GROUP BY TITLE;

Here, you're doing Cross Join between tables MOVIE & ROLE. Which leads you to wrong result.

在这里，您正在表、电影和角色之间进行交叉连接。这会导致错误的结果。

What you need here is Inner Join of these 2 tables based on the common column MOVIEID.

这里需要的是基于公共列MOVIEID的这两个表的内部连接。

Following query should work:

以下查询应该:

SELECT m.TITLE, COUNT(r.MOVIEID) AS NUMROLES
FROM MOVIE m
inner join ROLE r
on m.MOVIEID = r.MOVIEID
GROUP BY m.TITLE
having NUMROLES >=6;

having NUMROLES >=6 is used to filter out TITLE with 5 or less than 5 counts.

使用NUMROLES >=6来过滤包含5个或少于5个计数的标题。

You can learn more about JOIN by clicking here.

点击这里可以了解更多关于JOIN的信息。

#1