Clever Geek Handbook

How do I get a list of numbers in MySQL?

I have a database of films and I would like to have a list of years when I do not have a movie this year. So all I need is a list (1900 .. 2012) and then I can JOIN AND SIGN IN AND NOT SIGN IN to whatever I want.

I have:

CREATE PROCEDURE build_years(p1 SMALLINT) 
BEGIN 
    CREATE TEMPORARY TABLE year (year SMALLINT(5) UNSIGNED); 
    label1: LOOP 
        INSERT INTO year VALUES (p1); 
        SET p1 = p1 + 1; 
        IF p1 > 2012 THEN LEAVE label1; END IF; 
    END LOOP; 
END

But it looks like this unSQL and only marginally less kludgy and then runs Python code to create the same table. I would really love the fact that it didn't use a stored procedure, didn't use a loop, and didn't use the actual table in that order.

+2


source to share


6 answers


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

1. Sequence table

I created a table filled with an integer sequence from 0 to <as needed>:

CREATE TABLE numbers (n INT);
INSERT INTO numbers VALUES (0),(1),(2),(3),(4);
INSERT INTO numbers SELECT n+5 FROM numbers;
INSERT INTO numbers SELECT n+10 FROM numbers;
INSERT INTO numbers SELECT n+20 FROM numbers;
INSERT INTO numbers SELECT n+40 FROM numbers;
etc.

It is executed only once, so it can even be created manually from outside your application.

2. Select the data type and range required

For integers this is obvious - that is, the range is 1.99:



SELECT n FROM numbers WHERE n BETWEEN 1 AND 99;

Dates - from 2 to 2 days:

SELECT date_add(now(),INTERVAL 2*n HOUR) FROM numbers WHERE n BETWEEN 0 AND 23;

So, in your case, it could be:

SELECT n+1900 AS n_year FROM numbers WHERE n BETWEEN 0 AND 112;

Then JOIN

on n_year

.

+1


source


This should work until you need more than 195 years, after which you will need to add UNION ALL

:

SELECT Year 
FROM   (   SELECT @i:= @i + 1 AS YEAR
           FROM   INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY,
                  ( SELECT @i:= 1899) AS i
        ) As Y
WHERE   Year BETWEEN 1900 AND 2012
ORDER BY Year;


Although I am assuming the COLLATION_CHARACTER_SET_APPLICABILITY

System table has a default size of 195 based on my reliable SQL Fiddle test site

+4


source


This will return a list from 2012 to 1900 if you really want to store it in the query.

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


source


The only solution I can think of, according to your wishes, too ...

SELECT years.year FROM
    (
        SELECT 1900 AS year
        UNION SELECT 1901
        ...
        UNION SELECT 2012
    ) AS years
    LEFT OUTER JOIN yourmovietable USING (year)
WHERE yourmovietable.year IS NULL;
0


source


Using this general query is faster:

INSERT INTO numbers SELECT n+(SELECT COUNT(*) FROM numbers) FROM numbers;

The execution of each request is duplicated:

INSERT INTO numbers VALUES (0),(1),(2),(3),(4);

INSERT INTO numbers SELECT n+(SELECT COUNT(*) FROM numbers) FROM numbers;
INSERT INTO numbers SELECT n+(SELECT COUNT(*) FROM numbers) FROM numbers;
INSERT INTO numbers SELECT n+(SELECT COUNT(*) FROM numbers) FROM numbers;

...

0


source 


select year into temporary table blaa from (generate_series(1900,2000)) where year not in(select distinct(year) from films)

Don't know if this will work, but you will get drift.

-1


source






More articles:


All Articles