MySQL 选择其中行大于 1
我正在研究一个查询，该查询连接多个表以深入了解按订阅者拥有不止一票的区域进行投票的订阅者数量。
我遇到的问题是试图在查询中写入计算订阅者投票超过一票的位置的查询。
SELECT COUNT(*), regions.name,
(SELECT COUNT(*) FROM votes
LEFT JOIN profiles on votes.subscriber_id = profiles.subscriber_id
LEFT JOIN countries on profiles.country_id = countries.id
WHERE countries.region_id = regions.id GROUP BY votes.subscriber_id) as vote_count
FROM subscribers
LEFT JOIN profiles on subscribers.id = profiles.subscriber_id
LEFT JOIN countries on profiles.country_id = countries.id
LEFT JOIN regions on countries.region_id = regions.id
LEFT JOIN votes on subscribers.id = votes.subscriber_id
WHERE subscribers.created_at < '2011-04-22 00:00:00'
GROUP BY regions.id
HAVING vote_count > 1;
通过上述查询，我收到错误：子查询返回超过 1 行
有什么想法吗？
I am working on a query that is joining multiple tables to drill down to get the number of subscribers who voted by region where subscribers have more than one vote.
The issue I am stuck at is trying to write in the query of figuring out the calculation of where subscribers voted more than one.
SELECT COUNT(*), regions.name,
(SELECT COUNT(*) FROM votes
LEFT JOIN profiles on votes.subscriber_id = profiles.subscriber_id
LEFT JOIN countries on profiles.country_id = countries.id
WHERE countries.region_id = regions.id GROUP BY votes.subscriber_id) as vote_count
FROM subscribers
LEFT JOIN profiles on subscribers.id = profiles.subscriber_id
LEFT JOIN countries on profiles.country_id = countries.id
LEFT JOIN regions on countries.region_id = regions.id
LEFT JOIN votes on subscribers.id = votes.subscriber_id
WHERE subscribers.created_at < '2011-04-22 00:00:00'
GROUP BY regions.id
HAVING vote_count > 1;
With the above query I am getting the error of, Subquery returns more than 1 row
Any ideas?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
该部分
返回多个结果。尝试删除“GROUP BY votes.subscriber_id”，这应该可以解决问题
The section
is returning more than one result. Try removing the "GROUP BY votes.subscriber_id" which should solve the problem
如果您的子查询：
返回超过 1 行，那么它将出错，因为它试图将行集合放入值
vote_count中。您可以通过添加HAVING子句来保证查询只有 1 行结果来解决此问题。if your subquery:
returns more than 1 row, then it will error, because it's trying to put a collection of rows into the value
vote_count. You could solve this by guaranteeing the query has only 1 row result by adding aHAVINGclause.由于您将子查询放在查询的 SELECT 部分中，因此只需要一个返回值（因为结果集是二维的）。
您必须在内部查询的 WHERE 子句中的某处添加与外部查询的匹配，以确保只有一行匹配。
您要查找与regions.name 匹配的COUNT(*) 条内容？您必须将内部查询连接到它。
Since you place the sub query in the SELECT part of the query, only one return value is expected (since result sets are two dimensional).
You have to add somewhere in the inner query's WHERE clause a match to the outer query to make sure only one row matches.
What COUNT(*) is it you're looking for that matches regions.name? You have to connect the inner query to that.
尝试将
HAVING vote_count > >子查询中的 1子句。另外，单独尝试子查询，看看它会产生什么结果。Try putting the
HAVING vote_count > 1clause in the subquery. Also, try the subquery on its own to see what it's yielding.