mysql 假选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/937523/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql fake select
提问by omg
My purpose is: to get multiple rows from a value list,like (1,2,3,4,5),('a','b','c','anything') and so on.
我的目的是：从值列表中获取多行，例如 (1,2,3,4,5),('a','b','c','anything') 等等。
mysql> select id from accounts where id in (1,2,3,4,5,6);
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 5 |
| 6 |
+----+
5 rows in set (0.00 sec)
The above sql is surely ok,but my question is:is there a way to get the same result without
上面的sql肯定没问题，但我的问题是：有没有办法在没有
specifying a table?Because my purpose here is just to propagate rows by an id_set
指定一个表？因为我在这里的目的只是通过 id_set 传播行
another example:
另一个例子：
mysql> select now() as column1;
+---------------------+
| column1 |
+---------------------+
| 2009-06-01 20:59:33 |
+---------------------+
1 row in set (0.00 sec)
mysql>
This example propagated a single row result without specifying a table,
此示例传播单行结果而不指定表，
but how to propagate multiple rows from a string like (1,2,3,4,5,6)?
但是如何从像 (1,2,3,4,5,6) 这样的字符串传播多行？
回答by Frank V
Something like this should work:
这样的事情应该工作：
SELECT *
FROM (
SELECT 0 as id
UNION SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
)
回答by Can Berk Güder
MySQL has a dummy table: DUAL. but using DUAL doesn't change anything (it's just for convenience), and certainly doesn't make this query work.
MySQL 有一个虚拟表：DUAL。但是使用 DUAL 不会改变任何东西（只是为了方便），当然也不会使这个查询起作用。
I'm sure there's a better way to achieve what you're trying to do. We might be able to help if you explain your problem.
我相信有更好的方法来实现您的目标。如果您能解释您的问题，我们可能会提供帮助。
回答by Evert
This does not answer your question exactly, but I believe this will fix your actual problem..
这并不能完全回答您的问题，但我相信这会解决您的实际问题。
SET @counter = 0; SELECT (@counter := @counter + 1 as counter) ... rest of your query
设置@counter = 0; SELECT (@counter := @counter + 1 as counter) ... 查询的其余部分
回答by Alex Martelli
One technique I've found invaluable is an "integers table", which lets you easily do all kinds of neat things including this one (xaprb has written several blog posts on this technique and the closely related "mutex table" one).
我发现一种非常宝贵的技术是“整数表”，它可以让您轻松地做各种整洁的事情，包括这个（xaprb 已经写了几篇关于这种技术和密切相关的“互斥表”的博客文章）。
回答by Tom Schaefer
A simple and old fashioned way is to use a table which holds consecutive values.
一种简单而老式的方法是使用包含连续值的表。
DROP TABLE IF EXISTS `range10`;
CREATE TABLE IF NOT EXISTS `range10` (
`id` int(11) NOT NULL,
KEY `id` (`id`)
) ENGINE=MyISAM;
INSERT INTO `range10` (`id`) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Once installed you can write queries as shown below.
安装后，您可以编写如下所示的查询。
get every second row:
获取每第二行：
select * from your_data_table where id in (
SELECT id*2 as id FROM `range10` WHERE id in(
select id from `range10`
)
)
get rows from 1101 to 1111:
获取从 1101 到 1111 的行：
select * from your_data_table where id in (
SELECT id+1100 as id FROM `range10` WHERE id in(
select id from `range10`
)
)
So if you are in the need of greater ranges, then just increase the size of the consecutive values in table range10. Querying is simple, cost are low, no stored procedure or function needed.
因此，如果您需要更大的范围，那么只需增加表 range10 中连续值的大小即可。查询简单，成本低，不需要存储过程或函数。
Note:
笔记：
You can create a table with consecutive char values, too. But varying the contents would not be so easy.
您也可以创建一个包含连续字符值的表。但改变内容不会那么容易。
