with tb(Num, Name, Date) as( select '01', '张三', 20021102 union all select '02', '李四', 20021101 union all select '03', '赵武', 20021103 union all select '03' ,'赵武', 20021123 )select Num,Name,max(Date) from tb group by Num,Name order by Num
IF ( object_id('a') is not null ) DROP TABLE a GO CREATE TABLE a ( num varchar(2), [name] varchar(4), date datetime ) go insert into a select '01','张三','20021102' union all select '02','李四','20021101' union all select '03','赵武','20021103' union all select '03','赵武','20021123' goselect num as '编号',[name] as '名字',max(date) as '日期' from a group by num,[name] /* 编号 名字 日期 ---- ---- ----------------------- 02 李四 2002-11-01 00:00:00.000 01 张三 2002-11-02 00:00:00.000 03 赵武 2002-11-23 00:00:00.000(3 行受影响) */
select num,name,max(date) from querytable group by num,name
聚合不行,那就不用group。使用以下:select num,name,date from ( select *,ps=row_number()over(partition by num order by date desc) from tablename )a where a.ps=1
access中这样应该是可以的吧? declare @T table (Num varchar(2),Name varchar(4),Date datetime) insert into @T select '01','张三','20021102' union all select '02','李四','20021101' union all select '03','赵武','20021103' union all select '03','赵武','20021123'select * from @T t where Date=(select max(Date) from @T where Num=t.Num and Name=t.Name) 把@T 直接换成表名试试。
select * from @T t where Date=(select max(Date) from @T where Num=t.Num and Name=t.Name and Date between '20021101' and '20021111')
with tb(Num, Name, Date)
as(
select '01', '张三', 20021102 union all
select '02', '李四', 20021101 union all
select '03', '赵武', 20021103 union all
select '03' ,'赵武', 20021123
)select Num,Name,max(Date) from tb group by Num,Name order by Num
IF ( object_id('a') is not null ) DROP TABLE a
GO
CREATE TABLE a
(
num varchar(2),
[name] varchar(4),
date datetime
)
go
insert into a
select '01','张三','20021102' union all
select '02','李四','20021101' union all
select '03','赵武','20021103' union all
select '03','赵武','20021123'
goselect num as '编号',[name] as '名字',max(date) as '日期'
from a
group by num,[name]
/*
编号 名字 日期
---- ---- -----------------------
02 李四 2002-11-01 00:00:00.000
01 张三 2002-11-02 00:00:00.000
03 赵武 2002-11-23 00:00:00.000(3 行受影响)
*/
兄台,倘若条件在复杂一点。比如如何选出介于某个时间段之间的最后一个日期的信息。比如:
Num Name Date
01 张三 20021102
02 李四 20021101
02 李四 20021110
03 赵武 20021103
03 赵武 20021123
03 赵武 20021124
03 赵武 20021110
选出介于20021101和20021111之间的最大的那个信息,结果应该是:
01 张三 20021102
02 李四 20021110
03 赵武 20021110
from (
select *,ps=row_number()over(partition by num order by date desc)
from tablename
)a where a.ps=1
declare @T table (Num varchar(2),Name varchar(4),Date datetime)
insert into @T
select '01','张三','20021102' union all
select '02','李四','20021101' union all
select '03','赵武','20021103' union all
select '03','赵武','20021123'select * from @T t
where Date=(select max(Date) from @T where Num=t.Num and Name=t.Name)
把@T 直接换成表名试试。
where Date=(select max(Date) from @T where Num=t.Num and Name=t.Name
and Date between '20021101' and '20021111')