时间点[24小时制]:早6:30-晚:20:30原始表Table1数据记录:
Rid(varchar) begindate(datetiem) enddate (datetiem ) Uid(varchar,UserID)
1 2010-04-15 06:00:00.000 2010-04-15 07:00:00.000 U001
2 2010-04-15 08:19:00.000 2010-04-15 12:19:00.000 U001
3 2010-04-15 15:19:00.000 2010-04-15 19:19:00.000 U001
4 2010-04-16 05:19:00.000 2010-04-16 19:19:00.000 U001
5 2010-04-16 05:19:00.000 2010-04-16 20:35:00.000 U002
6 2010-04-16 05:29:00.000 010-04-16 18:35:00.000 U002想要的返回的结果集[即我需要写的SQL语句]:Rid begindate enddate Uid(UserID) Flag
1 2010-04-15 06:00:00.000 2010-04-15 07:00:00.000 U001 NULL
2 2010-04-15 08:19:00.000 2010-04-15 12:19:00.000 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-16 05:29:00.000 2010-04-16 18:35:00.00 U002 C计算规则[请看清楚:不用考虑跨天、跨月、跨年]:
对于一天只有一条记录的:
[情况1].如果begindate日期的时分<=6.30[对应上面的6:30AM]且同时enddate的时分>=20.30[对应上面的20:30PM],则Flag标记为"A";(如:Rid=5的记录:begindate时分为5.19,要小于6.30,且同时enddate的时分为20.35,大于20.30,所以标记为"A".)[情况2].只要满足begindate日期的时分>6.30一个条件,则Flag标记为"B"(此时不用同时考虑enddate的时分部分)[如:Rid=4的记录];[情况3].如果begindate日期的时分<=6.30 ,且同时满足enddate日期的时分<20.30,则Flag标记为"C"[如:Rid=3的记录];
对于一天多条记录的(实际上和上面的一天仅一条记录规则相同,只是取begindate及enddate日期的时分是从首末记录中取两个数据)[如:共3条,则从第1条中egindate的时分,从第3条取enddate的时分,且之标记第3条的Flag,其它2条的Flag均为NULL]:[情况1].如果当天首次begindate日期的时分<=6.30 且同时当天末次enddate的时分>=20.30,则Flag标记为"A";[上面原始数据未列出此情况][情况2].如果当天首次begindate日期的时分>6.30 ,则Flag标记为"B"(此时不用同时不用考虑当天末次出库时间);[上面原始数据未列出此情况][情况3].如果当天首次begindate日期的时分<=6.30 且同时当天末次enddate的时分<20.30,则Flag标记为"C"(如:上面Rid为1,2,3的记录:Rid为1的begindate的时分为06.0,即0.6<=6.30,且Rid为3的enddate的时分19.19<20.30,所以Rid3的Flag标记"C",Rid1、Rid2为NULL[同一天只标记最后一个.] )
注意及提醒:1.假如上面情况[无论一天单条或多条记录]存在交叉的(貌似没有吧?)只要按照此优先级显示:情况1>情况2>情况3
2.Rid是varchar[为了方便写成数字1,2,3],请勿作为计算规则[影响实际结果集情况下]用来order by.
3.[一天单记录when case 好写,卡在一天多条的],取begindate的时分我用到了 CAST (CAST(DATEPART(HOUR,begindate) AS VARCHAR(10))+'.' + CAST (DATEPART(MINUTE,begindate) AS VARCHAR(10)) AS FLOAT) enddate 类似.[6:30AM即6.30,20:30即20.30]
Rid(varchar) begindate(datetiem) enddate (datetiem ) Uid(varchar,UserID)
1 2010-04-15 06:00:00.000 2010-04-15 07:00:00.000 U001
2 2010-04-15 08:19:00.000 2010-04-15 12:19:00.000 U001
3 2010-04-15 15:19:00.000 2010-04-15 19:19:00.000 U001
4 2010-04-16 05:19:00.000 2010-04-16 19:19:00.000 U001
5 2010-04-16 05:19:00.000 2010-04-16 20:35:00.000 U002
6 2010-04-16 05:29:00.000 010-04-16 18:35:00.000 U002想要的返回的结果集[即我需要写的SQL语句]:Rid begindate enddate Uid(UserID) Flag
1 2010-04-15 06:00:00.000 2010-04-15 07:00:00.000 U001 NULL
2 2010-04-15 08:19:00.000 2010-04-15 12:19:00.000 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-16 05:29:00.000 2010-04-16 18:35:00.00 U002 C计算规则[请看清楚:不用考虑跨天、跨月、跨年]:
对于一天只有一条记录的:
[情况1].如果begindate日期的时分<=6.30[对应上面的6:30AM]且同时enddate的时分>=20.30[对应上面的20:30PM],则Flag标记为"A";(如:Rid=5的记录:begindate时分为5.19,要小于6.30,且同时enddate的时分为20.35,大于20.30,所以标记为"A".)[情况2].只要满足begindate日期的时分>6.30一个条件,则Flag标记为"B"(此时不用同时考虑enddate的时分部分)[如:Rid=4的记录];[情况3].如果begindate日期的时分<=6.30 ,且同时满足enddate日期的时分<20.30,则Flag标记为"C"[如:Rid=3的记录];
对于一天多条记录的(实际上和上面的一天仅一条记录规则相同,只是取begindate及enddate日期的时分是从首末记录中取两个数据)[如:共3条,则从第1条中egindate的时分,从第3条取enddate的时分,且之标记第3条的Flag,其它2条的Flag均为NULL]:[情况1].如果当天首次begindate日期的时分<=6.30 且同时当天末次enddate的时分>=20.30,则Flag标记为"A";[上面原始数据未列出此情况][情况2].如果当天首次begindate日期的时分>6.30 ,则Flag标记为"B"(此时不用同时不用考虑当天末次出库时间);[上面原始数据未列出此情况][情况3].如果当天首次begindate日期的时分<=6.30 且同时当天末次enddate的时分<20.30,则Flag标记为"C"(如:上面Rid为1,2,3的记录:Rid为1的begindate的时分为06.0,即0.6<=6.30,且Rid为3的enddate的时分19.19<20.30,所以Rid3的Flag标记"C",Rid1、Rid2为NULL[同一天只标记最后一个.] )
注意及提醒:1.假如上面情况[无论一天单条或多条记录]存在交叉的(貌似没有吧?)只要按照此优先级显示:情况1>情况2>情况3
2.Rid是varchar[为了方便写成数字1,2,3],请勿作为计算规则[影响实际结果集情况下]用来order by.
3.[一天单记录when case 好写,卡在一天多条的],取begindate的时分我用到了 CAST (CAST(DATEPART(HOUR,begindate) AS VARCHAR(10))+'.' + CAST (DATEPART(MINUTE,begindate) AS VARCHAR(10)) AS FLOAT) enddate 类似.[6:30AM即6.30,20:30即20.30]
取第一条begin,第三条 end,不管它标记什么,但说明了余下的都为NULL.可是我看到上面2010-04-16 那三条上,没有哪条标NULL的啊!
1.上面原始数据Rid 4是一天一条记录啊(一个用户:U001,一条记录4.16日,以天为区分单位.)可是我看到上面2010-04-16 那三条上,没有哪条标NULL的啊!
2.只在结果集标明Flag的值:
结果集:
Rid begindate enddate Uid(UserID) Flag
1 2010-04-15 06:00:00.000 2010-04-15 07:00:00.000 U001 NULL [标明了NULL]
2 2010-04-15 08:19:00.000 2010-04-15 12:19:00.000 U001 NULL [标明了NULL]
3 2010-04-15 15:19:00.000 2010-04-15 19:19:00.000 U001 C [标明了C]
说明:上面Rid1,2,3为一个用户一天多条记录
4 2010-04-16 15:19:00.000 2010-04-16 19:19:00.000 U001 B [标明了B]
4为一个用户U001一天单条记录
5 2010-04-16 05:19:00.000 2010-04-16 20:35:00.000 U002 A [标明了A]
5 为一个用户U002一天单条记录
6 2010-04-17 05:29:00.000 2010-04-17 18:35:00.00 U002 C [标明了C]
6 为一个用户U002一天单条记录(对应的原始数据RID6,begindate和enddate都改为2010-04-17就好了)
Rid(varchar) begindate(datetiem) enddate (datetiem ) Uid(varchar,UserID)
1 2010-04-15 06:00:00.000 2010-04-15 07:00:00.000 U001
2 2010-04-15 08:19:00.000 2010-04-15 12:19:00.000 U001
3 2010-04-15 15:19:00.000 2010-04-15 19:19:00.000 U001
4 2010-04-16 05:19:00.000 2010-04-16 19:19:00.000 U001
5 2010-04-16 05:19:00.000 2010-04-16 20:35:00.000 U002
6 2010-04-17 05:29:00.000 2010-04-17 18:35:00.000 U002
想要的返回的结果集[即我需要写的SQL语句]:Rid begindate enddate Uid(UserID) Flag[只有返回集才有Flag]
1 2010-04-15 06:00:00.000 2010-04-15 07:00:00.000 U001 NULL
2 2010-04-15 08:19:00.000 2010-04-15 12:19:00.000 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.00 U002 C即按上述计算规则,统计每个[不同]用户每天的Flag(或A,或B,或C)
(当然对于一天多条的,只有最后一条记录标明Flag[或A或B或C],同一天其它的为NULL)
1.如果一个用户一天一条记录,按上面说明的一天单记录规则标识。(case when可直接搞定)
2.如果一个用户一天多条记录,还是按上面说明的一天多记录规则,同一天只标识只最后一条记录的Flag值(或A,或B,或C),而该用户该天前面的记录的Flag标为NULL。
4 2010-04-16 05:19:00.000 2010-04-16 19:19:00.000 U001
而返回的结果集里头却是:
4 2010-04-16 15:19:00.000 2010-04-16 19:19:00.000 U001 B我说怎么也弄不到那个B的!坑谁哪~~~
6 2010-04-16 05:29:00.000 2010-04-16 18:35:00.00 U002 C又是什么回事呢?
返回的结果集里头应该是:
4 2010-04-16 05:19:00.000 2010-04-16 19:19:00.000 U001 B
抱歉,手误!
6 2010-04-16 05:29:00.000 2010-04-16 18:35:00.00 U002 C
又是什么回事呢?回复12楼:6应该是:
6 2010-04-17 05:29:00.000 2010-04-17 18:35:00.00 U002 C这个在5楼,我再发一遍了.敬请谅解
只是我说的太具体了(文字很多,反倒看起来貌似觉得很复杂),又加上给的数据有手误[都怪me]!
有劳诸位高手!期待中...
insert into tb select '1','2010-04-15 06:00:00.000','2010-04-15 07:00:00.000','U001'
insert into tb select '2','2010-04-15 08:19:00.000','2010-04-15 12:19:00.000','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
select *,
(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 not exists(select 1 from tb where uid=a.uid and rid!=a.rid and convert(varchar(10),begindate,120)=convert(varchar(10),a.begindate,120))
union all
select *,null
from tb a
where exists(select 1 from tb where uid=a.uid and rid>a.rid and convert(varchar(10),begindate,120)=convert(varchar(10),a.begindate,120))
union all
select *,
(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(
select max(rid)rid,min(begindate)begindate,max(enddate)enddate,uid from tb group by uid,convert(varchar(10),begindate,120) having count(*)>1
)a
order by rid
/*
Rid begindate enddate Uid flag
---------- ----------------------- ----------------------- ---------- ----
1 2010-04-15 06:00:00.000 2010-04-15 07:00:00.000 U001 NULL
2 2010-04-15 08:19:00.000 2010-04-15 12:19:00.000 U001 NULL
3 2010-04-15 06:00: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(6 行受影响)*/
go
drop table tb
回复:恩,总之原始数据和返回数据集要对应起来(相同)。
我先把您的SQL应用到我的实际问题中(要相对复杂些)....先谢谢了!
insert into Table1 values('1', '2010-04-15 06:00:00.000', '2010-04-15 07:00:00.000', 'U001')
insert into Table1 values('2', '2010-04-15 08:19:00.000', '2010-04-15 12:19:00.000', 'U001')
insert into Table1 values('3', '2010-04-15 15:19:00.000', '2010-04-15 19:19:00.000', 'U001')
insert into Table1 values('4', '2010-04-16 15:19:00.000', '2010-04-16 19:19:00.000', 'U001')
insert into Table1 values('5', '2010-04-16 05:19:00.000', '2010-04-16 20:35:00.000', 'U002')
insert into Table1 values('6', '2010-04-16 05:29:00.000', '2010-04-16 18:35:00.000', 'U002')
goselect q1.* , null flag from table1 q1 where not exists(select 1 from
(
select m.* , case when convert(varchar(5),m.begindate,108) <= '06:30' and convert(varchar(5),m.enddate,108) >= '20:30' then 'A'
when convert(varchar(5),m.begindate,108) > '06:30' then 'B'
when convert(varchar(5),m.begindate,108) <= '06:30' and convert(varchar(5),m.enddate,108) < '20:30' then 'C' end flag
from table1 m,
(select Uid , convert(varchar(10),begindate,120) begindate from table1 group by Uid , convert(varchar(10),begindate,120) having count(1) = 1) n
where m.uid = n.uid and datediff(day,m.begindate,n.begindate) = 0
union all
select k1.* , k2.flag from table1 k1 ,
(
select k.begindate , k.enddate,k.uid,case when convert(varchar(5),k.begindate,108) <= '06:30' and convert(varchar(5),k.enddate,108) >= '20:30' then 'A'
when convert(varchar(5),k.begindate,108) > '06:30' then 'B'
when convert(varchar(5),k.begindate,108) <= '06:30' and convert(varchar(5),k.enddate,108) < '20:30' then 'C' end flag from
(
select min(m.begindate) begindate, max(m.enddate) enddate, convert(varchar(10),m.begindate,120) date, uid from table1 m where not exists(select 1 from (select Uid , convert(varchar(10),begindate,120) begindate from table1 group by Uid , convert(varchar(10),begindate,120) having count(1) = 1) n where m.uid = n.uid and datediff(day,m.begindate,n.begindate) = 0)
group by convert(varchar(10),m.begindate,120), m.uid
) k
) k2 where k1.uid = k2.uid and k1.enddate = k2.enddate
) q2
where q1.uid = q2.uid and q1.enddate = q2.enddate
)
union all
select m.* , case when convert(varchar(5),m.begindate,108) <= '06:30' and convert(varchar(5),m.enddate,108) >= '20:30' then 'A'
when convert(varchar(5),m.begindate,108) > '06:30' then 'B'
when convert(varchar(5),m.begindate,108) <= '06:30' and convert(varchar(5),m.enddate,108) < '20:30' then 'C' end flag
from table1 m,
(select Uid , convert(varchar(10),begindate,120) begindate from table1 group by Uid , convert(varchar(10),begindate,120) having count(1) = 1) n
where m.uid = n.uid and datediff(day,m.begindate,n.begindate) = 0
union all
select k1.* , k2.flag from table1 k1 ,
(
select k.begindate , k.enddate,k.uid,case when convert(varchar(5),k.begindate,108) <= '06:30' and convert(varchar(5),k.enddate,108) >= '20:30' then 'A'
when convert(varchar(5),k.begindate,108) > '06:30' then 'B'
when convert(varchar(5),k.begindate,108) <= '06:30' and convert(varchar(5),k.enddate,108) < '20:30' then 'C' end flag from
(
select min(m.begindate) begindate, max(m.enddate) enddate, convert(varchar(10),m.begindate,120) date, uid from table1 m where not exists(select 1 from (select Uid , convert(varchar(10),begindate,120) begindate from table1 group by Uid , convert(varchar(10),begindate,120) having count(1) = 1) n where m.uid = n.uid and datediff(day,m.begindate,n.begindate) = 0)
group by convert(varchar(10),m.begindate,120), m.uid
) k
) k2 where k1.uid = k2.uid and k1.enddate = k2.enddate
order by riddrop table table1/*
Rid begindate enddate Uid flag
---------- ------------------------------------------------------ ------------------------------------------------------ ---------- ----
1 2010-04-15 06:00:00.000 2010-04-15 07:00:00.000 U001 NULL
2 2010-04-15 08:19:00.000 2010-04-15 12:19:00.000 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-16 05:29:00.000 2010-04-16 18:35:00.000 U002 NULL(所影响的行数为 6 行)
*/你的结果集貌似有错.仔细看了你的条件,貌似第六行的flag应该为null.
5 2010-04-16 05:19:00.000 2010-04-16 20:35:00.000 U002
6 2010-04-16 05:29:00.000 010-04-16 18:35:00.000 U002
5 2010-04-16 05:19:00.000 2010-04-16 20:35:00.000 U002
6 2010-04-16 05:29:00.000 010-04-16 18:35:00.000 U002结果集第六行和原数据第六行日期都应该是: (4.16应该是4.17,要不然就不符合一天单条记录了[我原本要表达的意思])
6 2010-04-17 05:29:00.000 2010-04-17 18:35:00.00 U002 C 可是两位大大:我实际的Rid是uniqueidentifier类型的,怎么办呢?
create table Table1(begindate datetime, enddate datetime, Uid varchar(10))
insert into Table1 values('2010-04-15 06:00:00.000', '2010-04-15 07:00:00.000', 'U001')
insert into Table1 values('2010-04-15 08:19:00.000', '2010-04-15 12:19:00.000', 'U001')
insert into Table1 values('2010-04-15 15:19:00.000', '2010-04-15 19:19:00.000', 'U001')
insert into Table1 values('2010-04-16 15:19:00.000', '2010-04-16 19:19:00.000', 'U001')
insert into Table1 values('2010-04-16 05:19:00.000', '2010-04-16 20:35:00.000', 'U002')
insert into Table1 values('2010-04-17 05:29:00.000', '2010-04-17 18:35:00.000', 'U002')
goselect q1.begindate,q1.enddate,q1.uid , null flag from table1 q1 where not exists(select 1 from
(
select m.begindate,m.enddate,m.uid , case when convert(varchar(5),m.begindate,108) <= '06:30' and convert(varchar(5),m.enddate,108) >= '20:30' then 'A'
when convert(varchar(5),m.begindate,108) > '06:30' then 'B'
when convert(varchar(5),m.begindate,108) <= '06:30' and convert(varchar(5),m.enddate,108) < '20:30' then 'C' end flag
from table1 m,
(select Uid , convert(varchar(10),begindate,120) begindate from table1 group by Uid , convert(varchar(10),begindate,120) having count(1) = 1) n
where m.uid = n.uid and datediff(day,m.begindate,n.begindate) = 0
union all
select k1.begindate,k1.enddate,k1.uid , k2.flag from table1 k1 ,
(
select k.begindate , k.enddate,k.uid,case when convert(varchar(5),k.begindate,108) <= '06:30' and convert(varchar(5),k.enddate,108) >= '20:30' then 'A'
when convert(varchar(5),k.begindate,108) > '06:30' then 'B'
when convert(varchar(5),k.begindate,108) <= '06:30' and convert(varchar(5),k.enddate,108) < '20:30' then 'C' end flag from
(
select min(m.begindate) begindate, max(m.enddate) enddate, convert(varchar(10),m.begindate,120) date, uid from table1 m where not exists(select 1 from (select Uid , convert(varchar(10),begindate,120) begindate from table1 group by Uid , convert(varchar(10),begindate,120) having count(1) = 1) n where m.uid = n.uid and datediff(day,m.begindate,n.begindate) = 0)
group by convert(varchar(10),m.begindate,120), m.uid
) k
) k2 where k1.uid = k2.uid and k1.enddate = k2.enddate
) q2
where q1.uid = q2.uid and q1.enddate = q2.enddate
)
union all
select m.begindate,m.enddate,m.uid , case when convert(varchar(5),m.begindate,108) <= '06:30' and convert(varchar(5),m.enddate,108) >= '20:30' then 'A'
when convert(varchar(5),m.begindate,108) > '06:30' then 'B'
when convert(varchar(5),m.begindate,108) <= '06:30' and convert(varchar(5),m.enddate,108) < '20:30' then 'C' end flag
from table1 m,
(select Uid , convert(varchar(10),begindate,120) begindate from table1 group by Uid , convert(varchar(10),begindate,120) having count(1) = 1) n
where m.uid = n.uid and datediff(day,m.begindate,n.begindate) = 0
union all
select k1.begindate,k1.enddate,k1.uid , k2.flag from table1 k1 ,
(
select k.begindate , k.enddate,k.uid,case when convert(varchar(5),k.begindate,108) <= '06:30' and convert(varchar(5),k.enddate,108) >= '20:30' then 'A'
when convert(varchar(5),k.begindate,108) > '06:30' then 'B'
when convert(varchar(5),k.begindate,108) <= '06:30' and convert(varchar(5),k.enddate,108) < '20:30' then 'C' end flag from
(
select min(m.begindate) begindate, max(m.enddate) enddate, convert(varchar(10),m.begindate,120) date, uid from table1 m where not exists(select 1 from (select Uid , convert(varchar(10),begindate,120) begindate from table1 group by Uid , convert(varchar(10),begindate,120) having count(1) = 1) n where m.uid = n.uid and datediff(day,m.begindate,n.begindate) = 0)
group by convert(varchar(10),m.begindate,120), m.uid
) k
) k2 where k1.uid = k2.uid and k1.enddate = k2.enddate
order by uid,begindate,enddate
drop table table1/*
begindate enddate uid flag
------------------------------------------------------ ------------------------------------------------------ ---------- ----
2010-04-15 06:00:00.000 2010-04-15 07:00:00.000 U001 NULL
2010-04-15 08:19:00.000 2010-04-15 12:19:00.000 U001 NULL
2010-04-15 15:19:00.000 2010-04-15 19:19:00.000 U001 C
2010-04-16 15:19:00.000 2010-04-16 19:19:00.000 U001 B
2010-04-16 05:19:00.000 2010-04-16 20:35:00.000 U002 A
2010-04-17 05:29:00.000 2010-04-17 18:35:00.000 U002 C(所影响的行数为 6 行)
*/
我(实际可能)需要加上rid列,因为它是主键[作其它用途],我赶紧试试您25楼的SQL.
insert into Table1 values(1,'2010-04-15 06:00:00.000', '2010-04-15 07:00:00.000', 'U001')
insert into Table1 values(2,'2010-04-15 08:19:00.000', '2010-04-15 12:19:00.000', 'U001')
insert into Table1 values(3,'2010-04-15 15:19:00.000', '2010-04-15 19:19:00.000', 'U001')
insert into Table1 values(4,'2010-04-16 15:19:00.000', '2010-04-16 19:19:00.000', 'U001')
insert into Table1 values(5,'2010-04-16 05:19:00.000', '2010-04-16 20:35:00.000', 'U002')
insert into Table1 values(6,'2010-04-17 05:29:00.000', '2010-04-17 18:35:00.000', 'U002')
goselect q1.rid,q1.begindate,q1.enddate,q1.uid , null flag from table1 q1 where not exists(select 1 from
(
select m.rid,m.begindate,m.enddate,m.uid , case when convert(varchar(5),m.begindate,108) <= '06:30' and convert(varchar(5),m.enddate,108) >= '20:30' then 'A'
when convert(varchar(5),m.begindate,108) > '06:30' then 'B'
when convert(varchar(5),m.begindate,108) <= '06:30' and convert(varchar(5),m.enddate,108) < '20:30' then 'C' end flag
from table1 m,
(select Uid , convert(varchar(10),begindate,120) begindate from table1 group by Uid , convert(varchar(10),begindate,120) having count(1) = 1) n
where m.uid = n.uid and datediff(day,m.begindate,n.begindate) = 0
union all
select k1.rid,k1.begindate,k1.enddate,k1.uid , k2.flag from table1 k1 ,
(
select k.begindate , k.enddate,k.uid,case when convert(varchar(5),k.begindate,108) <= '06:30' and convert(varchar(5),k.enddate,108) >= '20:30' then 'A'
when convert(varchar(5),k.begindate,108) > '06:30' then 'B'
when convert(varchar(5),k.begindate,108) <= '06:30' and convert(varchar(5),k.enddate,108) < '20:30' then 'C' end flag from
(
select min(m.begindate) begindate, max(m.enddate) enddate, convert(varchar(10),m.begindate,120) date, uid from table1 m where not exists(select 1 from (select Uid , convert(varchar(10),begindate,120) begindate from table1 group by Uid , convert(varchar(10),begindate,120) having count(1) = 1) n where m.uid = n.uid and datediff(day,m.begindate,n.begindate) = 0)
group by convert(varchar(10),m.begindate,120), m.uid
) k
) k2 where k1.uid = k2.uid and k1.enddate = k2.enddate
) q2
where q1.uid = q2.uid and q1.enddate = q2.enddate
)
union all
select m.rid,m.begindate,m.enddate,m.uid , case when convert(varchar(5),m.begindate,108) <= '06:30' and convert(varchar(5),m.enddate,108) >= '20:30' then 'A'
when convert(varchar(5),m.begindate,108) > '06:30' then 'B'
when convert(varchar(5),m.begindate,108) <= '06:30' and convert(varchar(5),m.enddate,108) < '20:30' then 'C' end flag
from table1 m,
(select Uid , convert(varchar(10),begindate,120) begindate from table1 group by Uid , convert(varchar(10),begindate,120) having count(1) = 1) n
where m.uid = n.uid and datediff(day,m.begindate,n.begindate) = 0
union all
select k1.rid,k1.begindate,k1.enddate,k1.uid , k2.flag from table1 k1 ,
(
select k.begindate , k.enddate,k.uid,case when convert(varchar(5),k.begindate,108) <= '06:30' and convert(varchar(5),k.enddate,108) >= '20:30' then 'A'
when convert(varchar(5),k.begindate,108) > '06:30' then 'B'
when convert(varchar(5),k.begindate,108) <= '06:30' and convert(varchar(5),k.enddate,108) < '20:30' then 'C' end flag from
(
select min(m.begindate) begindate, max(m.enddate) enddate, convert(varchar(10),m.begindate,120) date, uid from table1 m where not exists(select 1 from (select Uid , convert(varchar(10),begindate,120) begindate from table1 group by Uid , convert(varchar(10),begindate,120) having count(1) = 1) n where m.uid = n.uid and datediff(day,m.begindate,n.begindate) = 0)
group by convert(varchar(10),m.begindate,120), m.uid
) k
) k2 where k1.uid = k2.uid and k1.enddate = k2.enddate
order by uid,begindate,enddatedrop table table1/*
rid begindate enddate uid flag
---------- ------------------------------------------------------ ------------------------------------------------------ ---------- ----
1 2010-04-15 06:00:00.000 2010-04-15 07:00:00.000 U001 NULL
2 2010-04-15 08:19:00.000 2010-04-15 12:19:00.000 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(所影响的行数为 6 行)*/
大大:如果我将上面的Table1和其它表关联的话,(实际中需要,如:left join )直接在from table1 关联即可吧,[where再跟上相关条件]
这么说有些含糊,明天早上我再实践一下,确实不早了,您二位大大也早休息吧!