mysql查询where IN语句
我想要执行以下操作:
SELECT count(id) FROM table WHERE value BETWEEN 3 AND 40;
但它应该执行以下操作:
SELECT count(id) FROM table WHERE value IN(3, 4, 5, 6, 7, 8, 9, 10, 11, ..., 40);
它甚至应该打印出 3 到 40 之间的值的零计数(id),但不是 value = x。我想检查某个值是否在序列 (1, 2, 3, 4, ..., 50) 中。
有谁知道如何用mysql实现这一点?
谢谢。
I want to do the following:
SELECT count(id) FROM table WHERE value BETWEEN 3 AND 40;
But it should do the following:
SELECT count(id) FROM table WHERE value IN(3, 4, 5, 6, 7, 8, 9, 10, 11, ..., 40);
It should even print out zero count(id) for value between 3 and 40, but not value = x. I want to check if a value is in a sequence (1, 2, 3, 4, ..., 50).
Does anyone know how to achieve this with mysql?
Thanks.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
MySQL 没有递归功能,因此您只能使用 NUMBERS 表技巧 -
创建一个仅保存递增数字的表 - 使用 auto_increment 很容易做到:
使用以下方式填充表:
...您需要的任意数量的值。
这将返回您想要查看计数的值的列表:
LEFT JOIN 到现有表中以便能够查看 COUNT 为零的位置:
MySQL doesn't have recursive functionality, so you're left with using the NUMBERS table trick -
Create a table that only holds incrementing numbers - easy to do using an auto_increment:
Populate the table using:
...for as many values as you need.
This will return a list of the values you want to see counts for:
LEFT JOIN onto your existing table to be able to see where the COUNT is zero:
以下是您正在寻找的内容的猜测:
Here is a guess at what you are looking for:
假设我理解你的问题:
编辑:我想我知道你的意思
Assuming I understood your question:
Edit: I think I know what you mean
我认为您正在寻找的是类似的东西:
但这不会为您提供任何不存在的值的 count(id) = 0 行。
I think what you're looking for is something like:
But that won't give you any count(id) = 0 rows for values that don't exist.