How Do I Get A List Of Numbers In Mysql?

I've got a database of movies, and I'd like a list of years where I don't have a movie for that year. So all I need is a list (1900 .. 2012) and then I can JOIN and IN and NOT IN o

Solution 1:

This should work until you need more than 195 years , at which point you'll need to add a UNION ALL:

SELECTYearFROM   (   SELECT @i:= @i + 1 AS YEAR
           FROM   INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY,
                  ( SELECT @i:= 1899) AS i
        ) AsYWHEREYearBETWEEN1900AND2012ORDERBYYear;

Although I am assuming that the COLLATION_CHARACTER_SET_APPLICABILITY System table has a default size of 195 based on my trusty testing ground SQL Fiddle

Solution 2:

I had similar problem a few years ago. My solution was:

1. Sequence table

I created a table filled with integer sequence from 0 to < as much as it will be required >:

CREATETABLE numbers (n INT);
INSERTINTO numbers VALUES (0),(1),(2),(3),(4);
INSERTINTO numbers SELECT n+5FROM numbers;
INSERTINTO numbers SELECT n+10FROM numbers;
INSERTINTO numbers SELECT n+20FROM numbers;
INSERTINTO numbers SELECT n+40FROM numbers;
etc.

It is executed only once, so can be created from outside of your app, even by hand.

2. Select data of a needed type and range

For integers it is obvious - i.e. range 1..99:

SELECT n FROM numbers WHERE n BETWEEN1AND99;

Dates - 2h intervals from now to +2 days:

SELECT date_add(now(),INTERVAL2*n HOUR) FROM numbers WHERE n BETWEEN0AND23;

So in your case it could be:

SELECT n+1900AS n_year FROM numbers WHERE n BETWEEN0AND112;

Then JOIN it on n_year.

Solution 3:

This will return a list of 2012 to 1900 if you really want to keep it to a query..

SELECT 
    TO_CHAR (ADD_MONTHS (TRUNC (SYSDATE, 'YYYY'), ((rno -1) *-12)), 'YYYY') AS "years"
FROM 
    (
    SELECT 
        LEVEL rno
    FROM DUAL
    CONNECTBY LEVEL <=
                   (SELECT TO_CHAR (TRUNC (SYSDATE, 'YYYY'), 'YYYY')
                           -1899
                               yearstobuild
                      FROM DUAL))

Solution 4:

The only solution I can think of according to your wishes sucks also ...

SELECT years.year FROM
    (
        SELECT1900ASyearUNIONSELECT1901
        ...
        UNIONSELECT2012
    ) AS years
    LEFTOUTERJOIN yourmovietable USING (year)
WHERE yourmovietable.year ISNULL;

Solution 5:

Using this generic query is faster:

INSERTINTO numbers SELECT n+(SELECTCOUNT(*) FROM numbers) FROM numbers;    

Each query execution duplicates:

INSERTINTO numbers VALUES (0),(1),(2),(3),(4);

INSERTINTO numbers SELECT n+(SELECTCOUNT(*) FROM numbers) FROM numbers;
INSERTINTO numbers SELECT n+(SELECTCOUNT(*) FROM numbers) FROM numbers;
INSERTINTO numbers SELECT n+(SELECTCOUNT(*) FROM numbers) FROM numbers;

...