MySQL:高效返回组不为空的分组字段
在一个语句中,我尝试通过连接到另一个表来对一个表的行进行分组。我只想获取分组结果不为空的分组行。
前任。项目和类别
SELECT Category.id
FROM Item, Category
WHERE Category.id = Item.categoryId
GROUP BY Category.id
HAVING COUNT(Item.id) > 0
上面的查询给出了我想要的结果,但这很慢,因为它必须计算按 Category.id 分组的所有行。
有什么更有效的方法呢?
我试图执行 Group By LIMIT 以仅检索每组一行。但我的尝试却惨遭失败。知道我该怎么做吗?
谢谢
In one statement I'm trying to group rows of one table by joining to another table. I want to only get grouped rows where their grouped result is not empty.
Ex. Items and Categories
SELECT Category.id
FROM Item, Category
WHERE Category.id = Item.categoryId
GROUP BY Category.id
HAVING COUNT(Item.id) > 0
The above query gives me the results that I want but this is slow, since it has to count all the rows grouped by Category.id.
What's a more effecient way?
I was trying to do a Group By LIMIT to only retrieve one row per group. But my attempts failed horribly. Any idea how I can do this?
Thanks
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
试试这个:
这与您的原始查询类似,但不会执行您想要的操作。
如果您想选择非空类别,请执行以下操作:
为了快速执行此操作,请在
item (category_id)
上创建索引。Try this:
This is similar to your original query, but won't do what you want.
If you want to select non-empty categories, do this:
For this to work fast, create an index on
item (category_id)
.如果不需要的话,删除类别表怎么样?
我不确定您是否需要 HAVING 子句,因为如果类别中没有项目,则不会创建组。
What about eliminating the Category table if you don't need it?
I'm not sure you even need the HAVING clause since if there is no item in a category it won't create a group.
我认为这在功能上是等效的(返回至少有一个项目的每个类别),并且应该更快。
I think this is functionally equivalent (returns every category that has at least one item), and should be much faster.
我认为,这只是我的观点,正确的方法是计算所有的东西。也许问题出在另一个地方。
这就是我用来计数的方法,即使有大量数据,它的工作速度也相当快。
它将为您提供按类别列出的所有项目的哈希值。但它不会包含空类别。
然后,为了检索类别信息,请执行以下操作:
I think, and this is just my opinion, that the correct approach IS counting all the stuff. Maybe the problem is in another place.
This is what I use for counting and it works pretty fast, even with a lot of data.
It will give you a hash with all the items by category. But it will NOT include empty categories.
Then, for retrieveng category information do like this: