Re: Select enumeration of intergers
From: Tonkuma <tonkuma_at_jp.ibm.com>
Date: 17 Jul 2006 07:38:24 -0700
Message-ID: <1153147104.697942.62370_at_75g2000cwc.googlegroups.com>
WHERE value BETWEEN 510 AND 514
WHERE value BETWEEN 510 AND 514 Received on Mon Jul 17 2006 - 16:38:24 CEST
Date: 17 Jul 2006 07:38:24 -0700
Message-ID: <1153147104.697942.62370_at_75g2000cwc.googlegroups.com>
According to a "SQL Super Reference" written in Japanese (references 6
DBMS including MySQL),
MySQL support to specify multiple rows in VALUES.You no need to use
UNION and extra VIEW.
To be honest, I don't know performance characteristics of MySQL, so I'm
not sure how much influence for performance by following example. But,
at least SQL statement get simpler.
create view all_integers (value) as
select
d1.digit + d2.digit*10 + d3.digit*100 + .... + d10.digit*10000000000
from (VALUES 0,1,2,3,4,5,6,7,8,9) d1(digit)
, (VALUES 0,1,2,3,4,5,6,7,8,9) d2(digit)
, (VALUES 0,1,2,3,4,5,6,7,8,9) d3(digit)
......
, (VALUES 0,1,2,3,4,5,6,7,8,9) d10(digit)
Another idea is without using all_integers view, write only neccesary
part in every statement.
For example:
1)
SELECT value
FROM (select
d1.digit + d2.digit*10 + d3.digit*100 from (VALUES 0,1,2,3,4,5,6,7,8,9) d1(digit) , (VALUES 0,1,2,3,4,5,6,7,8,9) d2(digit) , (VALUES 0,1,2,3,4,5,6,7,8,9) d3(digit) ) S (value)
WHERE value BETWEEN 510 AND 514
or
2)
SELECT value
FROM (select
d1.digit + d2.digit*10 + 500 from (VALUES 0,1,2,3,4,5,6,7,8,9) d1(digit) , (VALUES 0,1,2,3,4,5,6,7,8,9) d2(digit) ) S (value)
WHERE value BETWEEN 510 AND 514 Received on Mon Jul 17 2006 - 16:38:24 CEST