MYSQL从开始/结束日期之间选择查询返回的月份列表作为字符串 [英] MYSQL select query return list of months as string from between start/end date

问题描述

合同表中有两个主要领域Startdate和Enddate我想将结果作为额外的列称为描述代表月份在合同开始和结束之间。请参阅下表和我需要的结果

There are two main fields Startdate and Enddate in Contract table I want to give result as extra column called description to represent month come between Contract start and end. see bellow table and result which I require

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 |
------------------------------

这里我需要结果按照下面的格式，一个额外的字段代表使用SELECT查询的合同的startdate和enddate之间的月份列表/列表。

Here I need result as per bellow formate, one extra field which represent range/list of months between startdate and enddate of contract using SELECT query.

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
----------------------------------------------------------------------------------------

上面的表startdate的第一行示例为0.001（2016-May-01），结束日期是2016-07-31 （2016年7月31日），所以这里列出了5月01日至7月31日期间的月份和年份，所以描述将是2016年6月 - 2016年6月，2016年7月 - 2016年。

Example first row of above table startdate is 2016-05-01 (2016-May-01) and end date is 2016-07-31 (2016-July-31) so here it gives list of months and year between May-01 to July-31 so description will be May-2016, Jun-2016, July-2016.

我尝试了许多查询，仍然无法获得确切的SQL查询。

I tried many queries still I fail to get the exact SQL query.

不知道如何做，并获得相同的结果，

Don't know how to do exactly and get same result,

任何建议请

感谢提前

推荐答案

以下查询应该做的伎俩。

The below query should do the trick.

您输入和输出的数据对于第二行是不同的，我已经运行它输出数据

Your data in input and output are different for 2nd row, I have run it for the output data

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, '%Y %M '))) 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;

这篇关于MYSQL从开始/结束日期之间选择查询返回的月份列表作为字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！