表 A
Order Date
001 2007－1－1
001 2007－1－1
001 2007－1－2
001 2007－1－2
002 2007－1－6
002 2007－1－6
002 2007－1－8
002 2007－1－8
要的结果是
Order Date
001 2007－1－2
001 2007－1－2
002 2007－1－8
002 2007－1－8
急啊，新人求解
------解决方案--------------------
create table A( [Order] nvarchar(10), [Date] nvarchar(20))
insert into a select '001 ', '2007-1-1 '
insert into a select '001 ' , '2007-1-1 '
insert into a select '001 ' , '2007-1-2 '
insert into a select '001 ' , '2007-1-2 '
insert into a select '002 ' , '2007-1-6 '
insert into a select '002 ' , '2007-1-6 '
insert into a select '002 ' , '2007-1-8 '
insert into a select '002 ' , '2007-1-8 '
select * from a
where not exists (select 1 from a b where a.[Order]=b.[Order] and a.[Date] <b.[Date])
Order Date
---------- --------------------
001 2007-1-2
001 2007-1-2
002 2007-1-8
002 2007-1-8
(4 row(s) affected)
------解决方案--------------------
---方法1
Select * From A As T Where Not Exists
(Select 1 From A Where [Order]=T.[Order] And [Date]> T.[Date])
Order By [Order],[Date]
---方法2
Select * From A Where [Date] In
(Select Max([Date]) From A Group By [Order])
Order By [Order],[Date]
------解决方案--------------------
create table tb([Order] varchar(10),Date datetime)
insert into tb values( '001 ', '2007-1-1 ')
insert into tb values( '001 ', '2007-1-1 ')
insert into tb values( '001 ', '2007-1-2 ')
insert into tb values( '001 ', '2007-1-2 ')
insert into tb values( '002 ', '2007-1-6 ')
insert into tb values( '002 ', '2007-1-6 ')
insert into tb values( '002 ', '2007-1-8 ')
insert into tb values( '002 ', '2007-1-8 ')
go
select * from tb where date in (select max(date) from tb group by [order])
drop table tb
/*
Order Date
---------- -----------------------
001 2007-01-02 00:00:00.000
001 2007-01-02 00:00:00.000
002 2007-01-08 00:00:00.000
002 2007-01-08 00:00:00.000
（所影响的行数为 4 行）
*/