当前位置: 代码迷 >> Sql Server >> 求句MAX的SQL语句解决方法
  详细解决方案

求句MAX的SQL语句解决方法

热度:96   发布时间:2016-04-27 19:37:49.0
求句MAX的SQL语句
表     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 行)
*/
  相关解决方案