select * from tb a where exists(select 1 from (select max(Postcount) Postcount,perday from tb group by perDay) b where b.Postcount=a.Postcount and b.perday=a.perday)
---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([Username] varchar(3),[Postcount] int,[perDay] datetime) insert [tb] select 'slj',10,'2008-10-1' union all select 'swa',8,'2008-10-1' union all select 'sw',8,'2008-10-2' union all select 'sa',3,'2008-10-2'
---查询--- select * from tb t where not exists(select 1 from tb where datediff(day,perDay,t.perDay)=0 and postcount>t.postcount)---结果--- Username Postcount perDay -------- ----------- ------------------------------------------------------ slj 10 2008-10-01 00:00:00.000 sw 8 2008-10-02 00:00:00.000(所影响的行数为 2 行)
create table tb(Username varchar(10), Postcount int, perDay datetime) go insert tb select 'slj', 10,' 2008-10-1' insert tb select 'swa', 8,' 2008-10-1' insert tb select 'sw', 8 ,'2008-10-2' insert tb select 'sa', 3 ,'2008-10-2' go select username,Postcount,convert(varchar(10),perday,120) perDay from tb a where exists(select 1 from (select max(Postcount) Postcount,perday from tb group by perDay) b where b.Postcount=a.Postcount and b.perday=a.perday) order by a.perday go drop table tb
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Username] varchar(3),[Postcount] int,[perDay] datetime)
insert [tb]
select 'slj',10,'2008-10-1' union all
select 'swa',8,'2008-10-1' union all
select 'sw',8,'2008-10-2' union all
select 'sa',3,'2008-10-2'
---查询---
select *
from tb t
where not exists(select 1 from tb where datediff(day,perDay,t.perDay)=0 and postcount>t.postcount)---结果---
Username Postcount perDay
-------- ----------- ------------------------------------------------------
slj 10 2008-10-01 00:00:00.000
sw 8 2008-10-02 00:00:00.000(所影响的行数为 2 行)
go
insert tb select 'slj', 10,' 2008-10-1'
insert tb select 'swa', 8,' 2008-10-1'
insert tb select 'sw', 8 ,'2008-10-2'
insert tb select 'sa', 3 ,'2008-10-2'
go
select username,Postcount,convert(varchar(10),perday,120) perDay from tb a where exists(select 1 from (select max(Postcount) Postcount,perday from tb group by perDay) b where b.Postcount=a.Postcount and b.perday=a.perday) order by a.perday
go
drop table tb