问题描述
我有以下代码可用于获取订阅者数量:
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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!