重复记录操作的问题，高手们快来帮忙啊！

[wqhzjl]

在一个表里，重复记录操作的问题：
一. 要计算WKNo、Date相同记录的Time值差，即：
1.判断表里记录 WKNo=WKNo and Date=Date? 如条件成立，那么判断
Time<12：00 ?
14:00<Time<18:00 ?
19:00<Time<23:59 ?
如条件成立，就计算相同记录的Time值差。


No. WKNo Date Time
-------------------------------------------
1 AB0003 20041229 10:35
2 AB0003 20041229 11:20
3 AB0003 20041229 15:35
4 AB0003 20041229 16:20
5 AC0128 20041214 09:21
6 AC0128 20041214 10:24
7 AC0128 20041214 19:21
8 AC0128 20041214 21:24

[Softlee81307]

Create Table cf(no int,WkNo varchar(10),date varchar(8),time varchar(5))
insert into cf
select 1,'AB0003','20041229','10:35' union all
select 2,'AB0003','20041229' ,'11:20' union all
select 3,'AB0003','20041229' ,'15:35' union all
select 4,'AB0003','20041229' , '16:20' union all
select 5,'AC0128','20041214' ,'09:21' union all
select 6,'AC0128','20041214','10:24' union all
select 7 ,'AC0128','20041214','19:21' union all
select 8,'AC0128','20041214','21:24'
------------------------------------------------------------------

------------------下面計算時差-----------------
select cf.*,sc=(case when a.no is not null then datediff(hh,cast(a.time as datetime),cast(cf.time as datetime)) else 0 end)
from cf left join cf a on(cf.no=a.no+1 and cf.wkno=a.wkno and cf.date=a.date)

----------------結果---------------------
No Wkno date time sc(時差)
1 AB0003 20041229 10:35 0
2 AB0003 20041229 11:20 1
3 AB0003 20041229 15:35 4
4 AB0003 20041229 16:20 1
5 AC0128 20041214 09:21 0
6 AC0128 20041214 10:24 1
7 AC0128 20041214 19:21 9
8 AC0128 20041214 21:24 2

[Softlee81307]

要加上排序
Create Table cf(no int,WkNo varchar(10),date varchar(8),time varchar(5))
insert into cf
select 1,'AB0003','20041229','10:35' union all
select 2,'AB0003','20041229' ,'11:20' union all
select 3,'AB0003','20041229' ,'15:35' union all
select 4,'AB0003','20041229' , '16:20' union all
select 5,'AC0128','20041214' ,'09:21' union all
select 6,'AC0128','20041214','10:24' union all
select 7 ,'AC0128','20041214','19:21' union all
select 8,'AC0128','20041214','21:24'
------------------------------------------------------------------

------------------下面計算時差-----------------
select cf.*,sc=(case when a.no is not null then datediff(hh,cast(a.time as datetime),cast(cf.time as datetime)) else 0 end)
from cf left join cf a on(cf.no=a.no+1 and cf.wkno=a.wkno and cf.date=a.date) order by cf.wkno,cf.date,cf.time

----------------結果---------------------
No Wkno date time sc(時差)
1 AB0003 20041229 10:35 0
2 AB0003 20041229 11:20 1
3 AB0003 20041229 15:35 4
4 AB0003 20041229 16:20 1
5 AC0128 20041214 09:21 0
6 AC0128 20041214 10:24 1
7 AC0128 20041214 19:21 9
8 AC0128 20041214 21:24 2

[Qihua_wu]

select WKNo,Date,时间段,count(*)
select WKNo,Date,Case when Time < 12:00 then '小于12点' else case when time > 14:00 and time <18:00 then '14点至18点之间' else when 19:00<Time and time <23:59 then '17点至24点之间' end end end as 时间段
from 表
group by WKNo,Date,时间段

[jinjazz]

create table test
(no integer,
WKNo char(6),
Date char(8),
Time char(5))
insert into test
select
1 , 'AB0003' ,'20041229' , '10:35'
union select
2, 'AB0003', '20041229', '11:20'
union select
3, 'AB0003', '20041229', '15:35'
union select
4, 'AB0003', '20041229', '16:20'
union select
5, 'AC0128', '20041214', '09:21'
union select
6, 'AC0128', '20041214', '10:24'
union select
7, 'AC0128', '20041214', '19:21'
union select
8, 'AC0128', '20041214', '21:24'

select cast(left(b.time1,2) as integer)*60+ cast(right(b.time1,2)as integer)-
cast(left(a.time1,2) as integer)*60+ cast(right(a.time1,2)as integer) as Time1,
cast(left(b.time2,2) as integer)*60+ cast(right(b.time2,2)as integer)-
cast(left(a.time2,2) as integer)*60+ cast(right(a.time2,2)as integer) as Time2,
cast(left(b.time3,2) as integer)*60+ cast(right(b.time3,2)as integer)-
cast(left(a.time3,2) as integer)*60+ cast(right(a.time3,2)as integer) as Time3
from
(select wkno,date,time1=min(case when time<'12:00' then time else '0' end),
time2=min(case when time>'14:00' and time<'18:00' then time else '0'end),
time3=min(case when time>'19:00' then time else '0' end)
from test
group by wkno,date) a,

(select wkno,date,time1=max(case when time<'12:00' then time else '0' end),
time2=max(case when time>'14:00' and time<'18:00' then time else '0' end),
time3=max(case when time>'19:00' then time else '0' end)
from test
group by wkno,date) b

where a.wkno=b.wkno and a.date=b.date

drop table test

[jinjazz]

修改了一下

create table test
(no integer,
WKNo char(6),
Date char(8),
Time char(5))
insert into test
select
1 , 'AB0003' ,'20041229' , '10:35'
union select
2, 'AB0003', '20041229', '11:20'
union select
3, 'AB0003', '20041229', '15:35'
union select
4, 'AB0003', '20041229', '16:20'
union select
5, 'AC0128', '20041214', '09:21'
union select
6, 'AC0128', '20041214', '10:24'
union select
7, 'AC0128', '20041214', '19:21'
union select
8, 'AC0128', '20041214', '21:24'

select a.wkno,a.date,
cast(left(b.time1,2) as integer)*60+ cast(right(b.time1,2)as integer)-
cast(left(a.time1,2) as integer)*60+ cast(right(a.time1,2)as integer) as 早,
cast(left(b.time2,2) as integer)*60+ cast(right(b.time2,2)as integer)-
cast(left(a.time2,2) as integer)*60+ cast(right(a.time2,2)as integer) as 中,
cast(left(b.time3,2) as integer)*60+ cast(right(b.time3,2)as integer)-
cast(left(a.time3,2) as integer)*60+ cast(right(a.time3,2)as integer) as 晚
from
(select wkno,date,time1=min(case when time<'12:00' then time else '0' end),
time2=min(case when time>'14:00' and time<'18:00' then time else '0'end),
time3=min(case when time>'19:00' then time else '0' end)
from test
group by wkno,date) a,

(select wkno,date,time1=max(case when time<'12:00' then time else '0' end),
time2=max(case when time>'14:00' and time<'18:00' then time else '0' end),
time3=max(case when time>'19:00' then time else '0' end)
from test
group by wkno,date) b

where a.wkno=b.wkno and a.date=b.date

drop table test


/*
结果

AB0003 20041229 680 980 0
AC0128 20041214 624 0 1284
*/

[jinjazz]

结果单位都是分钟

[wqhzjl]

先谢了，我现在就去试。