原帖:http://topic.csdn.net/u/20110910/13/c32fc055-9b90-4856-8c7e-36b0296610f3.html
已用您1楼正确的SQL。现想在您1楼原SQL基础上:(原规则不变。)
1.对于一天多记录的:返回结果末记录,欲将它的实际begindate值替换为首次的begindate值;同时返回首次的rid[如:rid_f];(对于一天一条记录的,它的begindate肯定不变,rid_f也如此.)
1楼原SQL返回结果:/*
Rid begindate enddate Uid flag
---------- ----------------------- ----------------------- ---------- ----
1 2010-04-15 06:00:00.000 NULL U001 NULL
2 2010-04-15 08:19:00.000 NULL U001 NULL
3 2010-04-15 15:19:00.000 2010-04-15 19:19:00.000 U001 C
4 2010-04-16 15:19:00.000 2010-04-16 19:19:00.000 U001 B
5 2010-04-16 05:19:00.000 2010-04-16 20:35:00.000 U002 A
6 2010-04-17 05:29:00.000 2010-04-17 18:35:00.000 U002 C
*/期望的结果(需写的SQL):/*
Rid Rid_f begindate enddate Uid flag
---- ------ ----------------------- ----------------------- ---------- ----
1 NULL 2010-04-15 06:00:00.000 NULL U001 NULL
2 NULL 2010-04-15 08:19:00.000 NULL U001 NULL
3 1 2010-04-15 06:00:00.000 2010-04-15 19:19:00.000 U001 C
4 4 2010-04-16 15:19:00.000 2010-04-16 19:19:00.000 U001 B
5 5 2010-04-16 05:19:00.000 2010-04-16 20:35:00.000 U002 A
6 6 2010-04-17 05:29:00.000 2010-04-17 18:35:00.000 U002 C
*/ps:您可以看到下面Rid为3的记录[一天多记录], Rid_f为当天首次的Rid值,所以为1,begindate为当天首次的
begindate值,所以是:2010-04-15 06:00:00.000.其它均未变. [一天单记录的情如上述.]
已用您1楼正确的SQL。现想在您1楼原SQL基础上:(原规则不变。)
1.对于一天多记录的:返回结果末记录,欲将它的实际begindate值替换为首次的begindate值;同时返回首次的rid[如:rid_f];(对于一天一条记录的,它的begindate肯定不变,rid_f也如此.)
1楼原SQL返回结果:/*
Rid begindate enddate Uid flag
---------- ----------------------- ----------------------- ---------- ----
1 2010-04-15 06:00:00.000 NULL U001 NULL
2 2010-04-15 08:19:00.000 NULL U001 NULL
3 2010-04-15 15:19:00.000 2010-04-15 19:19:00.000 U001 C
4 2010-04-16 15:19:00.000 2010-04-16 19:19:00.000 U001 B
5 2010-04-16 05:19:00.000 2010-04-16 20:35:00.000 U002 A
6 2010-04-17 05:29:00.000 2010-04-17 18:35:00.000 U002 C
*/期望的结果(需写的SQL):/*
Rid Rid_f begindate enddate Uid flag
---- ------ ----------------------- ----------------------- ---------- ----
1 NULL 2010-04-15 06:00:00.000 NULL U001 NULL
2 NULL 2010-04-15 08:19:00.000 NULL U001 NULL
3 1 2010-04-15 06:00:00.000 2010-04-15 19:19:00.000 U001 C
4 4 2010-04-16 15:19:00.000 2010-04-16 19:19:00.000 U001 B
5 5 2010-04-16 05:19:00.000 2010-04-16 20:35:00.000 U002 A
6 6 2010-04-17 05:29:00.000 2010-04-17 18:35:00.000 U002 C
*/ps:您可以看到下面Rid为3的记录[一天多记录], Rid_f为当天首次的Rid值,所以为1,begindate为当天首次的
begindate值,所以是:2010-04-15 06:00:00.000.其它均未变. [一天单记录的情如上述.]
insert into tb select '1','2010-04-15 06:00:00.000',null,'U001'
insert into tb select '2','2010-04-15 08:19:00.000',null,'U001'
insert into tb select '3','2010-04-15 15:19:00.000','2010-04-15 19:19:00.000','U001'
insert into tb select '4','2010-04-16 15:19:00.000','2010-04-16 19:19:00.000','U001'
insert into tb select '5','2010-04-16 05:19:00.000','2010-04-16 20:35:00.000','U002'
insert into tb select '6','2010-04-17 05:29:00.000','2010-04-17 18:35:00.000','U002'
go
;with c1 as(
select rid,uid,begindate dt from tb where begindate is not null
union all
select rid,uid,enddate from tb where enddate is not null
),c2 as(
select * from c1 a where not exists(select 1 from c1 where uid=a.uid and convert(varchar(10),dt,120)= convert(varchar(10),a.dt,120) and dt>a.dt)
),c3 as(
select * from c1 a where not exists(select 1 from c1 where uid=a.uid and convert(varchar(10),dt,120)= convert(varchar(10),a.dt,120) and dt<a.dt)
)select rid,rid as rid_f,begindate,enddate,uid,
(case when convert(varchar(5),begindate,108)<='06:30' and convert(varchar(5),enddate,108)>='20:30' then 'A'
when convert(varchar(5),begindate,108)<='06:30' and convert(varchar(5),enddate,108)<'20:30' then 'C'
else 'B' end)flag
from tb a
where exists(select 1 from c2 where uid=a.uid and rid=a.rid)
and exists(select 1 from c3 where uid=a.uid and rid=a.rid)
union all
select rid,null,begindate,enddate,uid,null
from tb a where not exists(select 1 from c2 where uid=a.uid and rid=a.rid)
union all
select rid,rid_f,dt,enddate,uid,
(case when convert(varchar(5),dt,108)<='06:30' and convert(varchar(5),enddate,108)>='20:30' then 'A'
when convert(varchar(5),dt,108)<='06:30' and convert(varchar(5),enddate,108)<'20:30' then 'C'
else 'B' end)flag
from (
select a.rid,a.uid,b.rid as rid_f,b.dt,c.begindate,a.dt as enddate from c2 a inner join c3 b on a.uid=b.uid and convert(varchar(10),a.dt,120)=convert(varchar(10),b.dt,120)
and not exists(select 1 from c3 where rid=a.rid)
inner join tb c on a.rid=c.rid
)a
order by rid
/*
rid rid_f begindate enddate uid flag
---------- ---------- ----------------------- ----------------------- ---------- ----
1 NULL 2010-04-15 06:00:00.000 NULL U001 NULL
2 NULL 2010-04-15 08:19:00.000 NULL U001 NULL
3 1 2010-04-15 06:00:00.000 2010-04-15 19:19:00.000 U001 C
4 4 2010-04-16 15:19:00.000 2010-04-16 19:19:00.000 U001 B
5 5 2010-04-16 05:19:00.000 2010-04-16 20:35:00.000 U002 A
6 6 2010-04-17 05:29:00.000 2010-04-17 18:35:00.000 U002 C(6 行受影响)*/
go
drop table tb