create table kaoqin ( id int , pname varchar(50), atime datetime )insert into kaoqin values(1,'zhangsan','2010-1-1 08:58:00') insert into kaoqin values(2,'lisi','2010-1-1 08:58:00') insert into kaoqin values(3,'zhangsan','2010-1-2 09:02:00') insert into kaoqin values(4,'lisi','2010-1-2 09:10:00') insert into kaoqin values(5,'zhangsan','2010-1-3 09:05:00') --drop table kaoqin select top 1 atime from kaoqin where pname in ( select top 1 pname from (select *,rank() over(partition by pname order by id) as num from kaoqin where datepart(hour,atime)>8 )a order by num desc ) order by atime desc
SELECT A.NAME,A.CNTS,A.LTIME FROM( SELECT NAME,COUNT(1)CNTS,MAX(TIME)LTIME,ROW_NUMBER()OVER(ORDER BY COUNT(1) DESC)ROWID FROM TB GROUP BY NAME WHERE datepart(hour,time)>8 )A WHERE A.ROWID=1
create table T_Table ( [Id] int primary key not null, [Name] varchar(20) null, [Time] datetime null )insert into T_Table values(1,'王五','2010-12-01 08:02:00') insert into T_Table values(2,'张三','2010-12-01 08:12:00') insert into T_Table values(3,'李四','2010-12-01 08:22:00') insert into T_Table values(4,'赵六','2010-12-01 08:32:00') insert into T_Table values(5,'赵六','2010-12-03 08:05:00') insert into T_Table values(6,'王五','2010-12-04 08:10:00') insert into T_Table values(7,'王五','2010-12-05 08:07:00')select * from T_Table--取最多的次数 select top 1 [Name],Count(1) from T_Table Group by [Name] order by Count(1) desc--取最后一次的迟到人的迟到时间 select [Name],max([Id]) iId,Max([Time]) dTime,Count(1) iCount from T_Table Group by [Name]--取最多的最后一次的那个人 select [Name],max([Id]) iId,Max([Time]) dTime,Count(1) iCount from T_Table Group by [Name] having Count(1)=(select top 1 Count(1) from T_Table Group by [Name] order by Count(1) desc)
修正下 SELECT A.NAME,A.CNTS,A.LTIME FROM( SELECT NAME,COUNT(1)CNTS,MAX(TIME)LTIME,ROW_NUMBER()OVER(ORDER BY COUNT(1) DESC)ROWID FROM TB WHERE datepart(hour,time)>8--9点来才算迟到? GROUP BY NAME )A WHERE A.ROWID=1
SQL2000不能一步出结果,得需要子查询或者临时表才能处理成功。
select max(dtime) from 考勤表 where name = ( select top 1 [Name] from 考勤表 where datepart(hour,dtime) > 8 Group by [Name] order by Count(*) desc) 取出来一个字段,最后迟到的次数。。如果同时存在2个人迟到次数一样多,只能选一个。。这方面不知道怎么解决
select max([name]),max([time]) from #TESTB where name = (select top 1 name from #TESTB where datepart(hour, [time])>8 group by name order by count(*) desc)
2005版本的话create table kaoqin ( id int , pname varchar(50), atime datetime )insert into kaoqin values(1,'zhangsan','2010-1-1 08:58:00') insert into kaoqin values(2,'lisi','2010-1-1 08:58:00') insert into kaoqin values(3,'zhangsan','2010-1-2 09:02:00') insert into kaoqin values(4,'lisi','2010-1-2 09:10:00') insert into kaoqin values(5,'zhangsan','2010-1-3 09:05:00') insert into kaoqin values(5,'wangwu','2010-1-1 08:05:00') insert into kaoqin values(5,'wangwu','2010-1-2 08:05:00') insert into kaoqin values(5,'wangwu','2010-1-3 09:05:00') insert into kaoqin values(5,'wangwu','2010-1-4 09:15:00') --drop table kaoqin select max(atime) as LLDtime,pname from kaoqin where pname in ( select pname from ( select pname,num from (select *,row_number() over(partition by pname order by id) as num from kaoqin where datepart(hour,atime)>8 )a ) pnames inner join (select top 1 num from (select *,row_number() over(partition by pname order by id) as num from kaoqin where datepart(hour,atime)>8 )a order by num desc) nums on pnames.num=nums.num ) group by pname
这样改一下select max(atime),pname from kaoqin where pname in ( select pname from ( select [pName],Count(*) as num from kaoqin where datepart(hour,atime) > 8 Group by [pName] ) a inner join ( select top 1 Count(*) as num from kaoqin where datepart(hour,atime) > 8 Group by [pName] order by Count(*) desc ) b on a.num=b.num ) group by pname
select top 1 max(id),atime from kaoqin group by pname,atime having max(id)>8 order by atime desc 看看这个能行不?
select top 1 max(id),atime from kaoqin group by pname,atime having max(id)>8 order by atime desc
select top 1 max(id),atime,pname from kaoqin group by pname,atime,pname having max(id)>8 order by atime desc
create table #kaoqin ( id int , name varchar(50), time datetime )insert into #kaoqin values(1,'zhangsan','2010-1-1 08:58:00') insert into #kaoqin values(2,'lisi','2010-1-1 08:58:00') insert into #kaoqin values(3,'zhangsan','2010-1-2 09:02:00') insert into #kaoqin values(4,'lisi','2010-1-2 09:10:00') insert into #kaoqin values(5,'zhangsan','2010-1-3 09:05:00')select max([time]) from #kaoqin where [name] in ( select top 1 [name] from ( select [name],count(*) cishu from ( select * from #kaoqin where datepart(hour,time)>8 ) a group by name ) b order by cishu desc )
create table kaoqin
(
id int ,
pname varchar(50),
atime datetime
)insert into kaoqin values(1,'zhangsan','2010-1-1 08:58:00')
insert into kaoqin values(2,'lisi','2010-1-1 08:58:00')
insert into kaoqin values(3,'zhangsan','2010-1-2 09:02:00')
insert into kaoqin values(4,'lisi','2010-1-2 09:10:00')
insert into kaoqin values(5,'zhangsan','2010-1-3 09:05:00')
--drop table kaoqin
select top 1 atime from kaoqin where pname in
(
select top 1 pname
from
(select *,rank() over(partition by pname order by id) as num from kaoqin where datepart(hour,atime)>8 )a
order by num desc
)
order by atime desc
SELECT A.NAME,A.CNTS,A.LTIME FROM(
SELECT NAME,COUNT(1)CNTS,MAX(TIME)LTIME,ROW_NUMBER()OVER(ORDER BY COUNT(1) DESC)ROWID FROM TB
GROUP BY NAME
WHERE datepart(hour,time)>8
)A
WHERE A.ROWID=1
create table T_Table
(
[Id] int primary key not null,
[Name] varchar(20) null,
[Time] datetime null
)insert into T_Table values(1,'王五','2010-12-01 08:02:00')
insert into T_Table values(2,'张三','2010-12-01 08:12:00')
insert into T_Table values(3,'李四','2010-12-01 08:22:00')
insert into T_Table values(4,'赵六','2010-12-01 08:32:00')
insert into T_Table values(5,'赵六','2010-12-03 08:05:00')
insert into T_Table values(6,'王五','2010-12-04 08:10:00')
insert into T_Table values(7,'王五','2010-12-05 08:07:00')select * from T_Table--取最多的次数
select top 1 [Name],Count(1) from T_Table Group by [Name] order by Count(1) desc--取最后一次的迟到人的迟到时间
select [Name],max([Id]) iId,Max([Time]) dTime,Count(1) iCount from T_Table Group by [Name]--取最多的最后一次的那个人
select [Name],max([Id]) iId,Max([Time]) dTime,Count(1) iCount from T_Table Group by [Name] having Count(1)=(select top 1 Count(1) from T_Table Group by [Name] order by Count(1) desc)
SELECT A.NAME,A.CNTS,A.LTIME FROM(
SELECT NAME,COUNT(1)CNTS,MAX(TIME)LTIME,ROW_NUMBER()OVER(ORDER BY COUNT(1) DESC)ROWID FROM TB
WHERE datepart(hour,time)>8--9点来才算迟到?
GROUP BY NAME
)A
WHERE A.ROWID=1
select top 1 [Name] from 考勤表
where datepart(hour,dtime) > 8 Group by [Name] order by Count(*) desc)
取出来一个字段,最后迟到的次数。。如果同时存在2个人迟到次数一样多,只能选一个。。这方面不知道怎么解决
(
id int ,
pname varchar(50),
atime datetime
)insert into kaoqin values(1,'zhangsan','2010-1-1 08:58:00')
insert into kaoqin values(2,'lisi','2010-1-1 08:58:00')
insert into kaoqin values(3,'zhangsan','2010-1-2 09:02:00')
insert into kaoqin values(4,'lisi','2010-1-2 09:10:00')
insert into kaoqin values(5,'zhangsan','2010-1-3 09:05:00')
insert into kaoqin values(5,'wangwu','2010-1-1 08:05:00')
insert into kaoqin values(5,'wangwu','2010-1-2 08:05:00')
insert into kaoqin values(5,'wangwu','2010-1-3 09:05:00')
insert into kaoqin values(5,'wangwu','2010-1-4 09:15:00')
--drop table kaoqin
select max(atime) as LLDtime,pname from kaoqin where pname in
(
select pname from
(
select pname,num
from
(select *,row_number() over(partition by pname order by id) as num from kaoqin where datepart(hour,atime)>8 )a
) pnames
inner join
(select top 1 num
from
(select *,row_number() over(partition by pname order by id) as num from kaoqin where datepart(hour,atime)>8 )a
order by num desc) nums on pnames.num=nums.num
)
group by pname
select pname from
( select [pName],Count(*) as num from kaoqin
where datepart(hour,atime) > 8 Group by [pName] ) a
inner join
( select top 1 Count(*) as num from kaoqin
where datepart(hour,atime) > 8 Group by [pName] order by Count(*) desc ) b
on a.num=b.num
)
group by pname
看看这个能行不?
(
id int ,
name varchar(50),
time datetime
)insert into #kaoqin values(1,'zhangsan','2010-1-1 08:58:00')
insert into #kaoqin values(2,'lisi','2010-1-1 08:58:00')
insert into #kaoqin values(3,'zhangsan','2010-1-2 09:02:00')
insert into #kaoqin values(4,'lisi','2010-1-2 09:10:00')
insert into #kaoqin values(5,'zhangsan','2010-1-3 09:05:00')select max([time]) from #kaoqin where [name] in
(
select top 1 [name] from
(
select [name],count(*) cishu from
(
select * from #kaoqin where datepart(hour,time)>8
) a group by name
) b order by cishu desc
)