--表tb 客户 customer 日期 date select * from tb t where not exists (select 1 from tb where customer = t.customer and date > t.dat)
select * from tb a where not exists(select 1 from tb where id=a.id and dt>a.id)
--如果tb表有主键,1楼的可能会出现重复记录 --表tb 客户 customer 日期 date select * from tb t where id = (select top 1 id from tb where customer = t.customer order by date desc)
--sql2005;with ach as ( select *,rid = row_number() over (partition by customer order by date desc) from tb )select * from ach where rid = 1
--表tb 客户 customer 日期 date
select *
from tb t
where not exists (select 1 from tb where customer = t.customer and date > t.dat)
--如果tb表有主键,1楼的可能会出现重复记录
--表tb 客户 customer 日期 date
select *
from tb t
where id = (select top 1 id from tb where customer = t.customer order by date desc)
--sql2005;with ach as
(
select *,rid = row_number() over (partition by customer order by date desc)
from tb
)select *
from ach
where rid = 1
create table tb(客户编号 int,货品编号 int,购买日期 datetime,购买数量 int)
insert into tb select 1,23,'2011-03-24',10
insert into tb select 2,21,'2011-03-25',15
insert into tb select 3,10,'2011-03-24',12
insert into tb select 1,22,'2011-03-27',18
insert into tb select 1,25,'2011-04-10',6
insert into tb select 2,15,'2011-04-05',12
insert into tb select 1,25,'2011-05-20',4
insert into tb select 3,27,'2011-05-21',5
go
select * from tb a where not exists(select 1 from tb where 客户编号=a.客户编号 and 购买日期>a.购买日期)
/*
客户编号 货品编号 购买日期 购买数量
----------- ----------- ----------------------- -----------
2 15 2011-04-05 00:00:00.000 12
1 25 2011-05-20 00:00:00.000 4
3 27 2011-05-21 00:00:00.000 5(3 行受影响)*/
go
drop table tb
select a.* from tb a,(select customer,max(date) date from tb group by customer) b where a.customer=b.customer and a.date=b.date