对于这个问题(MYSQL select query return list of months as string from between start/end date)我找到了查询的解决方案,它给出了正确的结果,但我需要按升序排列月份列表 .
Table : Contracts
------------------------------
ID | START | END |
------------------------------
1 | 2016-05-01 | 2016-07-31 |
2 | 2016-04-01 | 2016-08-31 |
3 | 2016-01-22 | 2016-02-25 |
4 | 2016-06-15 | 2017-11-30 |
------------------------------
在这里,我需要结果,如下所示,一个额外的字段,表示使用SELECT查询的 Contract 的startdate和enddate之间的月份/列表 .
Result (as per give format)
----------------------------------------------------------------------------------------
ID | START | END | Description
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | 2016-05-01 | 2016-07-31 | May-2016, Jun-2016, July-2016
2 | 2016-04-01 | 2016-07-31 | April-2016, May-2016, Jun-2016, July-2016
3 | 2016-01-22 | 2016-02-25 | January-2016, February-2016
3 | 2016-06-15 | 2017-11-30 | May-2017 ,November-2016 ,June-2016 ,August-2017 ,March-2017 ,July-2016 ,October-2016 ,November-2017 ,June-2017 ,February-2017 ,September-2016 ,September-2017 ,August-2016,April-2017 ,January-2017 ,July-2017 ,December-2016 ,October-2017
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL查询是:
Select id, DATE_FORMAT(start_Date, '%Y-%c-%d') as Start_Date,
DATE_FORMAT(end_date,'%Y-%c-%d') as END_Date,
group_concat( distinct(DATE_FORMAT(aDate, '%M %Y'))) as Descp
from (
select ss.end_date - interval (a.a ) month as aDate from
(select 0 as a union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6 union all
select 7 union all select 8 union all select 9) a, Contracts ss
) mon, Contracts sa
where aDate between sa.start_date and sa.end_date
group by id;
它像结果一样随机给出结果,即
"May-2017 ,November-2016 ,June-2016 ,August-2017 ,March-2017 ,July-2016 ,October-2016 ,November-2017 ,June-2017 ,February-2017 ,September-2016 ,September-2017 ,August-2016,April-2017 ,January-2017 ,July-2017 ,December-2016 ,October-2017"
但是我需要
"June-2016 ,July-2016 ,August-2016,September-2016,October-2016, November-2016 ,December-2016 ,January-2017 ,February-2017 ,March-2017 ,April-2017 ,May-2017 ,June-2017 ,July-2017 ,August-2017 ,September-2017 ,October-2017, November-2017
"
请帮我找到上述结果的解决方案,