CODEWITHSUNDEEP
mysqlselect
omg
omg

Reputation: 139962

mysql fake select

My purpose is: to get multiple rows from a value list,like (1,2,3,4,5),('a','b','c','anything') and so on.

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

specifying a table?Because my purpose here is just to propagate rows by an 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)?

Upvotes: 30

Views: 30419

Answers (6)

JCH77
JCH77

Reputation: 1363

Here a way to create custom rows directly with MySQL request SELECT :

SELECT ALL *
FROM (
    VALUES
        ROW ('1.1', '1.2', '1.3'),
        ROW ('2.1', '2.2', '2.3'),
        ROW ('3.1', '3.2', '3.3')
) AS dummy (c1, c2, c3)

Gives us a table dummy:

c1   c2   c3
-------------
1.1  1.2  1.3
2.1  2.2  2.3
3.1  3.2  3.3

Upvotes: 1

Frank V
Frank V

Reputation: 25429

Something like this should work:

SELECT 0 as id
UNION SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5

Afterwards, you can select what you need from it by giving it an alias:

SELECT *
FROM (
    SELECT 0 as id
    UNION SELECT 1
    UNION SELECT 2
    UNION SELECT 3
    UNION SELECT 4
    UNION SELECT 5
) `table1`

Upvotes: 35

Tom Schaefer
Tom Schaefer

Reputation: 897

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:

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.

Note:

You can create a table with consecutive char values, too. But varying the contents would not be so easy.

Upvotes: 1

Alex Martelli
Alex Martelli

Reputation: 882093

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).

Upvotes: 0

Evert
Evert

Reputation: 99667

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

Upvotes: 1

Can Berk Güder
Can Berk Güder

Reputation: 113370

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.

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.

Upvotes: 6

Related Questions