关于SQL查询生成周的超难问题.

[vfp_system]

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为连续的整数,表示周数.

[vfp_system]

UP

[xiaoku]

好像真的蛮难的!

[mengmou]

不明白，cFlag和flagdate是指什么？

[gc_ding]

create table riLi(d_date datetime,cFlag smallint)
insert rili
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-17',1

select
d_date_f=case when min(cFlag)=0 then min(d_date) end,
d_date_t=case when max(cFlag)=1 then max(d_date) end
from
riLi
group by
datepart(week,d_date)

drop table riLi

/*
d_date_f d_date_t
------------------------------------
2007-03-02 2007-03-03
2007-03-06 2007-03-09
2007-03-13 2007-03-17
*/

[gc_ding]

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-17',1

select
d_date_f=case when min(cFlag)=0 then min(d_date) end,
d_date_t=case when max(cFlag)=1 then max(d_date) end
from
riLi
group by
year(d_date),month(d_date),datepart(week,d_date)

drop table riLi

/*
d_date_f d_date_t
------------------------------------
2007-02-27 2007-02-28
2007-03-02 2007-03-03
2007-03-06 2007-03-09
2007-03-13 2007-03-17
*/

[mengmou]

还是不明白。

[vfp_system]

gc_ding(施主,给个妞泡好么)
你的方法还勉强行得通,但离我的要求还差一点.我再具体描述下:
1 自然周是指星期一到星期七;
2 cflageDate没有取出出来,它为下一周或以后的cflag标志为1的最小日期;
3 endDate要取标志位为1的大于当前日期并与当前日期在一周的并在本月的日期.

[vfp_system]

up

[vfp_system]

up

[vfp_system]

UP

[gc_ding]

--暂进只能做到同一周的，下班了...
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
*/

[gc_ding]

--打多了。。。这样可以了
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
*/

[w75251455]

看文字痛苦