求写个SQL

Question

表A中有开始时间 和结束时间表 A
StartDate      EndDate          ID
2011-05-08     2011-08-09       1拆分成
表B
ID  MonthName       StartDate        EndDate
1      5月         2011-05-08      2011-08-09
2      6月         2011-05-08      2011-08-09
3      7月         2011-05-08      2011-08-09
4      8月         2011-05-08      2011-08-09
 
需要考虑跨年

Answer 1

你这个不算拆分
自己连接master..spt_values表里的number加减法构建就可以了

Answer 2

if object_id('[TBA]') is not null drop table [TBA]
go
create table [TBA] (StartDate datetime,EndDate datetime,ID int)
insert into [TBA]
select '2011-05-08','2011-08-09',1select * from [TBA]SELECT  [ID] = ROW_NUMBER() OVER ( ORDER BY GETDATE() ) ,
        [月份] = CONVERT(VARCHAR, DATEPART(mm,
                                         DATEADD(mm, number, TBA.startdate)))
        + '月' ,
        [开始日期] = CONVERT(VARCHAR(10), TBA.startdate, 120) ,
        [截止日期] = CONVERT(VARCHAR(10), TBA.enddate, 120)
FROM    master..spt_values M
        INNER JOIN TBA ON DATEADD(mm, M.number, TBA.startdate) BETWEEN TBA.startdate
                                                              AND
                                                              TBA.EndDate
WHERE   M.type = 'P'
/*
ID                   月份                               开始日期       截止日期
-------------------- -------------------------------- ---------- ----------
1                    5月                               2011-05-08 2011-08-09
2                    6月                               2011-05-08 2011-08-09
3                    7月                               2011-05-08 2011-08-09
4                    8月                               2011-05-08 2011-08-09(4 行受影响)
*/

Answer 3

--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (StartDate varchar(10),EndDate varchar(10),ID int)
insert into [tb]
select '2011-05-08','2011-08-09',1--开始查询
select ID=row_number() over (order by a.number),
MonthName=ltrim(a.number)+'月',b.StartDate,b.EndDate 
from master..spt_values a join [tb] b 
on (a.number between datepart(MONTH,StartDate) and datepart(MONTH,EndDate))
where a.type='p'--结束查询
drop table [tb]/*
ID                   MonthName      StartDate  EndDate
-------------------- -------------- ---------- ----------
1                    5月             2011-05-08 2011-08-09
2                    6月             2011-05-08 2011-08-09
3                    7月             2011-05-08 2011-08-09
4                    8月             2011-05-08 2011-08-09(4 行受影响)

Answer 4

create table tb(StartDate datetime,EndDate datetime,ID int)
insert into tb select '2011-05-08','2011-08-09',1
go
;with cte as(
select id,ltrim(month(startdate))+'月' as monthName,StartDate,EndDate from tb
union all
select id+1,ltrim(replace(monthName,'月','')+1)+'月',startdate,enddate from cte where month(enddate)>=replace(monthName,'月','')+1
)select * from cte
/*
id          monthName      StartDate               EndDate
----------- -------------- ----------------------- -----------------------
1           5月             2011-05-08 00:00:00.000 2011-08-09 00:00:00.000
2           6月             2011-05-08 00:00:00.000 2011-08-09 00:00:00.000
3           7月             2011-05-08 00:00:00.000 2011-08-09 00:00:00.000
4           8月             2011-05-08 00:00:00.000 2011-08-09 00:00:00.000(4 行受影响)*/
go
drop table tb