create table jc_table(id int, name varchar(10) , cardid varchar(10),time datetime,status varchar(10))insert into jc_table values(1, '111', '111', '2007-8-15 08:00:22', '来')
insert into jc_table values(1, '111', '111', '2007-8-15 13:30:22', '迟到')
insert into jc_table values(2, '222', '222', '2007-8-15 08:00:22', '没来')
insert into jc_table values(2, '222', '222', '2007-8-15 13:30:22', '没来')
insert into jc_table values(3, '333', '333', '2007-8-15 08:00:22', '没来')
insert into jc_table values(3, '333', '333', '2007-8-15 13:30:22', '来')
insert into jc_table values(4, '4', '4', '2007-8-15 08:00:22', '来')
insert into jc_table values(4, '4', '4', '2007-8-15 13:30:22', '来')
select id,name,cardid,time=convert(varchar(10),time,120),
status=(case when exists(
select 1 from jc_table t where t.cardid=jc_table.cardid and t.time<>jc_table.time
and datediff(day,t.time,jc_table.time)=0 and t.status=jc_table.status and t.status='没来'
) then '没来' else '来' end)
from jc_table
where datepart(hh,time)<13
order by id,time---------------
id name cardid time status
----------- ---------- ---------- ---------- ------
1 111 111 2007-08-15 来
2 222 222 2007-08-15 没来
3 333 333 2007-08-15 来
4 4 4 2007-08-15 来
insert into jc_table values(1, '111', '111', '2007-8-15 13:30:22', '迟到')
insert into jc_table values(2, '222', '222', '2007-8-15 08:00:22', '没来')
insert into jc_table values(2, '222', '222', '2007-8-15 13:30:22', '没来')
insert into jc_table values(3, '333', '333', '2007-8-15 08:00:22', '没来')
insert into jc_table values(3, '333', '333', '2007-8-15 13:30:22', '来')
insert into jc_table values(4, '4', '4', '2007-8-15 08:00:22', '来')
insert into jc_table values(4, '4', '4', '2007-8-15 13:30:22', '来')
select id,name,cardid,time=convert(varchar(10),time,120),
status=(case when exists(
select 1 from jc_table t where t.cardid=jc_table.cardid and t.time<>jc_table.time
and datediff(day,t.time,jc_table.time)=0 and t.status=jc_table.status and t.status='没来'
) then '没来' else '来' end)
from jc_table
where datepart(hh,time)<13
order by id,time---------------
id name cardid time status
----------- ---------- ---------- ---------- ------
1 111 111 2007-08-15 来
2 222 222 2007-08-15 没来
3 333 333 2007-08-15 来
4 4 4 2007-08-15 来
insert into tb values(1, '111', '111', '2007-8-15 08:00:22', '来')
insert into tb values(1, '111', '111', '2007-8-15 13:30:22', '迟到')
insert into tb values(1, '111', '111', '2007-8-16 08:00:22', '没来')
insert into tb values(1, '111', '111', '2007-8-16 13:30:22', '没来')
insert into tb values(2, '222', '222', '2007-8-15 08:00:22', '没来')
insert into tb values(2, '222', '222', '2007-8-15 13:30:22', '没来')
insert into tb values(2, '222', '222', '2007-8-16 08:00:22', '来')
insert into tb values(2, '222', '222', '2007-8-16 13:30:22', '没来')
insert into tb values(3, '333', '333', '2007-8-15 08:00:22', '没来')
insert into tb values(3, '333', '333', '2007-8-15 13:30:22', '来')
goselect * from
(
select distinct id,name,cardid,convert(varchar(10),time,120) time , status = '来' from tb where status = '来' or status = '迟到'
union all
select id,name,cardid,convert(varchar(10),time,120) time ,status = '没来' from tb where status = '没来' group by id,name,cardid,convert(varchar(10),time,120) having count(*) = 2
) t
order by id,name,cardid,time
drop table tb
/*
id name cardid time status
----------- ---------- ---------- ---------- ------
1 111 111 2007-08-15 来
1 111 111 2007-08-16 没来
2 222 222 2007-08-15 没来
2 222 222 2007-08-16 来
3 333 333 2007-08-15 来(所影响的行数为 5 行)
*/
我的结果和乌龟的是一样的!create table jc_table(id int, name varchar(10) , cardid varchar(10),time datetime,status varchar(10))insert into jc_table values(1, '111', '111', '2007-8-15 08:00:22', '来')
insert into jc_table values(1, '111', '111', '2007-8-15 13:30:22', '迟到')
insert into jc_table values(1, '111', '111', '2007-8-16 08:00:22', '没来')
insert into jc_table values(1, '111', '111', '2007-8-16 13:30:22', '没来')
insert into jc_table values(2, '222', '222', '2007-8-15 08:00:22', '没来')
insert into jc_table values(2, '222', '222', '2007-8-15 13:30:22', '没来')
insert into jc_table values(2, '222', '222', '2007-8-16 08:00:22', '来')
insert into jc_table values(2, '222', '222', '2007-8-16 13:30:22', '没来')
insert into jc_table values(3, '333', '333', '2007-8-15 08:00:22', '没来')
insert into jc_table values(3, '333', '333', '2007-8-15 13:30:22', '来')select id,name,cardid,time=convert(varchar(10),time,120),
status=(case when exists(
select 1 from jc_table t where t.cardid=jc_table.cardid and t.time<>jc_table.time
and datediff(day,t.time,jc_table.time)=0 and t.status=jc_table.status and t.status='没来'
) then '没来' else '来' end)
from jc_table
where datepart(hh,time)<13
order by id,time
/*
id name cardid time status
----------- ---------- ---------- ---------- ------
1 111 111 2007-08-15 来
1 111 111 2007-08-16 没来
2 222 222 2007-08-15 没来
2 222 222 2007-08-16 来
3 333 333 2007-08-15 来(5 row(s) affected)
*/
请问一下
datepart(hh,time)<13 是什么意思啊?