从一个表中收集数据并在另一个表中计算记录数的SQL

Question

我有一个用户表和一个歌曲表，我想选择用户表中的所有用户，并计算他们在歌曲表中拥有多少首歌。我有这个SQL语句，但它无法正常工作，请问有人能发现我的错误吗？

SELECT jos_mfs_users.*, COUNT(jos_mfs_songs.id) as song_count 
FROM jos_mfs_users 
INNER JOIN jos_mfs_songs
ON jos_mfs_songs.artist=jos_mfs_users.id

非常感谢您的帮助。谢谢！

- Wasim

---

它的哪个方面出了问题？它是报错了还是结果出乎意料？ - Jonathan Hall

我猜每个记录的歌曲数量为1。 - Narnian

Answer 1

内连接无法工作，因为它将歌曲表中的每个匹配行都与用户表进行连接。

SELECT jos_mfs_users.*, 
    (SELECT COUNT(jos_mfs_songs.id) 
        FROM jos_mfs_songs
        WHERE jos_mfs_songs.artist=jos_mfs_users.id) as song_count 
FROM jos_mfs_users 
WHERE (SELECT COUNT(jos_mfs_songs.id) 
        FROM jos_mfs_songs
        WHERE jos_mfs_songs.artist=jos_mfs_users.id) > 10

- Narnian

---

谢谢你的帮助，它有效。如果我想在WHERE子句中使用song_count，因为当我放置WHERE song_count > 10时，它不起作用。我该如何引用song_count值？非常感谢您的帮助！ - Wasim

使用 HAVING 来将聚合函数（如 COUNT）作为比较条件。 - JNK

你可以在WHERE语句中加入(SELECT COUNT()...)子句。我会进行编辑以展示这一点。你也可以尝试使用"HAVING"子句。 - Narnian

SELECT A.*, (SELECT COUNT(*) FROM B WHERE B.a_id = A.id) AS TOT FROM A 是一个更为简单的解决方案。 - PUG

Answer 2

这里缺少一个GROUP BY子句，例如：

SELECT jos_mfs_users.id, COUNT(jos_mfs_songs.id) as song_count 
FROM jos_mfs_users 
INNER JOIN jos_mfs_songs
ON jos_mfs_songs.artist=jos_mfs_users.id
GROUP BY jos_mfs_users.id

如果您想从jos_mfs_users中添加更多列到选择列表中，您需要在GROUP BY子句中也加入它们。

- phlogratos

Answer 3

变更：

• Don't do SELECT *...specify your fields. I included ID and NAME, you can add more as needed but put them in the GROUP BY as well

• Changed to a LEFT JOIN - INNER JOIN won't list any users that have no songs

• Added the GROUP BY so it gives a valid count and is valid syntax

  SELECT u.id, u.name COUNT(s.id) as song_count 
  FROM jos_mfs_users AS u
  LEFT JOIN jos_mfs_songs AS S
      ON s.artist = u.id
  GROUP BY U.id, u.name
  

- JNK

Answer 4

尝试一下

SELECT 
  *, 
  (SELECT COUNT(*) FROM jos_mfs_songs as songs WHERE songs.artist=users.id) as song_count 
FROM 
  jos_mfs_users as users

- christopher_b

Answer 5

使用聚合函数（例如COUNT()）时，需要使用GROUP BY子句。

因此，假设jos_mfs_users.id是主键，则可以使用以下代码：

SELECT jos_mfs_users.*, COUNT( jos_mfs_users.id ) as song_count 
FROM jos_mfs_users 
INNER JOIN jos_mfs_songs
  ON jos_mfs_songs.artist = jos_mfs_users.id
GROUP BY jos_mfs_users.id

请注意

• 由于您正在按用户ID分组，因此您将在结果中获得每个不同用户ID的一个结果
• 您需要COUNT()的是正在分组的行数（在本例中是每个用户的结果数）

- edam

---

-1 对于在单个字段上使用 SELECT * 和 GROUP BY，他将会得到每个 users.id 随机记录，如果他的 SQL 引擎允许的话（大多数不会）。 - JNK

这个查询如何返回随机记录？对于按jos_mfs_users.id分组的每个结果行，jos_mfs_users中的所有字段都相同！而且没有从jos_mfs_songs中选择任何字段（只有一个COUNT()）。我认为你错了！ - edam

你正在假设所有其他字段都是相同的。OP没有表明它是一个唯一的字段或PK。在GROUP BY中选择不在其中或聚合中的字段是应该避免的反模式。这是毋庸置疑的。 - JNK

好的，你是正确的。我已经编辑了答案来表明我的假设。 - edam

Answer 6

这看起来像是一种多对多的关系。我的意思是，每个用户在 users 表格中可能会有几个记录，每个记录代表他们所拥有的一首歌曲。

我会建立三个表格：

• Users 表格，每个用户只有一条记录；
• Songs 表格，每首歌曲只有一条记录；
• USER_SONGS 表格，每个用户/歌曲组合只有一条记录。

现在，您可以通过查询中间表格来计算每个用户拥有多少首歌曲。您还可以找到拥有特定歌曲的用户数量。

这将告诉您每个用户有多少首歌曲。

select id, count(*) from USER_SONGS
GROUP BY id;

这将告诉您每首歌有多少用户

select artist, count(*) from USER_SONGS
GROUP BY artist;

我相信您需要根据自己的需求进行调整，但它可能会给您带来您正在寻找的结果类型。

您还可以将这两个查询之一加入另外两个表以查找用户名称和/或艺术家名称。

希望对您有所帮助
Harv Sather

附：我不确定您是否在寻找歌曲统计或艺术家统计。

- Harv