Select A Sequence Between Two Numbers On Mysql

I have this table named people with two dates on MySQL: | Name | start_date | end_date | | John | 2007-03-01 | 2009-10-12 | | Mike | 2001-06-06 | 2010-12-01 | I want to create a

Solution 1:

Something like this should do it:-

SELECT a.Name, GROUP_CONCAT(YEAR(DATE_ADD(a.start_date, INTERVAL b.aNum YEAR))) AS activity_years  
FROM person a
CROSSJOIN (SELECT a.i + b.i *10AS aNum FROM integers a, integers b) b
WHEREYEAR(DATE_ADD(a.start_date, INTERVAL b.aNum YEAR)) <=YEAR(a.end_date)
GROUPBY a.Name

It relies on a table of integers with a column called i, with the values 0 to 9. It joins this against itself to get a range of numbers from 0 to 99, so copes with date ranges that far apart.

Removing the subselects to use it in a view

SELECT p.Name, GROUP_CONCAT(YEAR(DATE_ADD(p.start_date, INTERVAL (a.i + b.i *10) YEAR))) AS activity_years  
FROM person p
CROSSJOIN integers a
CROSSJOIN integers b
WHEREYEAR(DATE_ADD(p.start_date, INTERVAL (a.i + b.i *10) YEAR)) <=YEAR(p.end_date)
GROUPBY p.Name

Solution 2:

SQLFiddle demo

select name,
       group_concat(tYears.row ORDERBY tYears.row)
  from people

join

(
SELECT@row :=@row+1asrowFROM 
(select0unionallselect1unionallselect3unionallselect4unionallselect5unionallselect6unionallselect6unionallselect7) t,
(select0unionallselect1unionallselect3unionallselect4unionallselect5unionallselect6unionallselect6unionallselect7) t2, 
(select0unionallselect1unionallselect3unionallselect4unionallselect5unionallselect6unionallselect6unionallselect7) t3, 

(SELECT@row:=1900) t21
) tYears

on tYears.row betweenyear(start_date) andyear(end_date)
groupby name