内联接mysql [英] INNER JOIN mysql

问题描述

我有以下代码可用于获取订阅者数量:

I have this code which I use to get the number of subscribers:

SELECT provider_id,subcat_id, COUNT(user_id) AS subscribers 
  FROM subscribers WHERE subcat_id=5 
    AND provider_id=provider_id 
  GROUP BY provider_id 
  ORDER BY subscribers DESC LIMIT 10 

provider_id是包含provider_name的providers表的外键.

provider_id is a foreign key to the providers table which contains the provider_name.

我如何获得获取provider_id外键值并将其替换为表providers的provider_name的结果?我尝试了内部联接，但是没有用.这可能是语法错误，因为我的选择查询有点复杂.

How can I get the results which obtains the foreign key value for provider_id and replaces it with the provider_name from table providers? I tried an inner join, but it didn't work. It is probably a syntax error as my select query is a bit complicated.

我现在得到的结果是:

+----------+---------+-----------+
|provder_id|subcat_id|subscribers|
+----------+---------+-----------+
|2         |5        |5          |
+----------+---------+-----------+
|4         |5        |3          |
+----------+---------+-----------+
|1         |5        |1          |
+----------+---------+-----------+

但是，我想要的是:

+----------+-------------+---------+-----------+
|provder_id|provider_name|subcat_id|subscribers|
+----------+-------------+---------+-----------+
|2         |Alex         |5        |5          |
+----------+-------------+---------+-----------+

SELECT subscriptions.provider_id，subscribers.subcat_id，COUNT(user_id)AS订阅者来自订阅者将提供者加入ON订阅者上按订户DESC LIMIT 10

SELECT subscribers.provider_id,subscribers.subcat_id, COUNT(user_id) AS subscribers FROM subscribers JOIN providers ON subscribers.provider_id=providers.provider_name WHERE subscribers.subcat_id=5 AND subscribers.provider_id=subscribers.provider_id GROUP BY subscribers.provider_id ORDER BY subscribers DESC LIMIT 10

那不起作用

解决了它

从订阅者的JOIN提供者中选择s.provider_id，p.provider_name，s.subcat_id，COUNT(s.user_id)AS订阅者p ON s.provider_id = p.provider_id WHERE s.subcat_id = 5 AND s.provider_id = s .provider_id GROUP BY provider_id ORDER BY订阅者DESC LIMIT 10

SELECT s.provider_id,p.provider_name, s.subcat_id, COUNT(s.user_id) AS subscribers FROM subscribers s JOIN providers p ON s.provider_id=p.provider_id WHERE s.subcat_id=5 AND s.provider_id=s.provider_id GROUP BY provider_id ORDER BY subscribers DESC LIMIT 10

推荐答案

尝试一下:

SELECT
  p.provider_id,
  provider.name,
  subcat_id,
  COUNT(user_id) AS subscribers 
FROM subscribers s
JOIN provider p ON p.provider_id = s.provider.id
WHERE subcat_id = 5
GROUP BY 1, 2, 3
ORDER BY 4 DESC 
LIMIT 10

请注意以下用途:

• 表的别名，以更清楚地区分常见的列名，
• 分组依据列出了所有非聚合列(您当前的查询将无法正常工作)

这篇关于内联接mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！