关于SQL查询生成周的超难问题.
create   table   riLi
(
d_date   datetime,
cFlag     smallint
)
create   table   t_week
(
weekNum   int,
startDate   datetime,
endDate       dateTime,
flagDate     datetime
)
上面是表结构,要求以riLi表的数据查询生成t_week表,具体条件是:
1.以自然周为分隔,一周中cflag为1的最小日期为startDate   ,
    一周中cflag为1的最大日期为endDate,下一周的cflag为1的最小日期为flagDate,
2.对于跨月的周要拆分为两周来处理.同样,对于跨年的周也要拆开来处理.
3.weekNum为连续的整数,表示周数.

------解决方案--------------------
--暂进只能做到同一周的，下班了...
create table riLi(d_date datetime,cFlag smallint)
insert rili
select '2007-2-27 ',0 union all
select '2007-2-28 ',1 union all
select '2007-3-2 ',0 union all
select '2007-3-3 ',1 union all
select '2007-3-6 ',0 union all
select '2007-3-8 ',1 union all
select '2007-3-9 ',1 union all
select '2007-3-14 ',0 union all
select '2007-3-13 ',0 union all
select '2007-3-16 ',1 union all
select '2007-3-17 ',1

select
weekNum=identity(int,1,1),
startDate=min(case when cFlag=0 then d_date end),
endDate=max(case when cFlag=1 then d_date end),
flagDate=min(case when cFlag=1 then d_date end)
into
#1
from
riLi
group by
year(d_date),month(d_date),datepart(week,d_date)

select
weekNum ,
startDate,
endDate,
flagDate=(select min(flagDate) from #1 where weekNum > a.weekNum)
from
#1 a

drop table riLi,#1

/*
weekNum startDate endDate flagDate
----------------------
1 2007-02-27 2007-02-28 2007-03-03
2 2007-03-02 2007-03-03 2007-03-08
3 2007-03-06 2007-03-09 2007-03-16
4 2007-03-13 2007-03-17 NULL
*/