在MySQL上选择两个数字之间的序列 [英] Select a sequence between two numbers on MySQL

问题描述

我有这个名为的人员，在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 |

我想创建一个视图，让我可以按活动年份搜索，即活动年份 start_date 和 end_date 之间的任何一年。所以，我想得到一个这样几年的字段：

I want to create a view that lets me search by activity year, being activity year any year between the start_date and the end_date. So, I'd like to get a field with a sequence of years, like this:

| Name | activity_years                                    |
| John | 2007,2008,2009                                    |
| Mike | 2001,2002,2003,2004,2005,2006,2007,2008,2009,2010 |

我尝试了一些方法，但我无法得到它。因为我想创建一个视图，所以我必须在SELECT语句里面做一切，这让我头疼。

I've tried some approaches, but I can't get it. Since I want to create a view, I have to do it everything inside a SELECT statement and that is giving me some headache.

推荐答案

这样做应该这样做： -

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
CROSS JOIN (SELECT a.i + b.i * 10 AS aNum FROM integers a, integers b) b
WHERE YEAR(DATE_ADD(a.start_date, INTERVAL b.aNum YEAR)) <= YEAR(a.end_date)
GROUP BY a.Name

它依赖于一个名为i的列的整数表，值为0到9.它与自身相连接以获得从0到99的数字范围，因此处理距离相近的日期范围。

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
CROSS JOIN integers a
CROSS JOIN integers b
WHERE YEAR(DATE_ADD(p.start_date, INTERVAL (a.i + b.i * 10) YEAR)) <= YEAR(p.end_date)
GROUP BY p.Name

这篇关于在MySQL上选择两个数字之间的序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！