求sql，分组求最大值

[yangjia921]

id customerid datetime value
1 1 2009-12-12 3

2 1 2009-12-12 4

3 2 2009-12-13 1

4 2 2009-14-14 2

我想查询出 每个客户最大 datetime的那条记录，如果存在datetime最大的记录有2条，取id较大的那条

查询结果：

2 1 2009-12-12 4
4 2 2009-14-14 2

[jiangshun]

select * from 表 t where not exists(select 1 from 表 where customerid=t.customerid and value<t.value)

[黄_瓜]

select * from 表 a where not exists
(
  select 1 from 表 where customerid=a.customerid and datetime <a.datetime 
)

[dawugui]

select t.* from tb t where not exists(select 1 from tb where customerid = t.customerid and (datetime > t.datetime or (datetime=t.datetime and id > t.id)))

[yangjia921]

楼上回帖的 请看清楚帖，现在我已近 用过你们写的方法了，如果最大值时间相同的记录有多条会查询出多条数据的，你们给的查询语句结果将会是


1 1 2009-12-12 3
2 1 2009-12-12 4
4 2 2009-14-14 2

[--小F--]

select
 *
from
 tb t
where
 id=(select max(id) from tb where customerid=a.customerid and datetime=t.datetime)

[--小F--]

select
 *
from
 tb t
where
 id=(select max(id) from tb where customerid=t.customerid and datetime=t.datetime)

[dawugui]

create table tb(id int, customerid int, [datetime] datetime, value int)
insert into tb values(1 , 1 , '2009-12-12' , 3 )
insert into tb values(2 , 1 , '2009-12-12' , 4 )
insert into tb values(3 , 2 , '2009-12-13' , 1 )
insert into tb values(4 , 2 , '2009-12-14' , 2 )
go

select t.* from tb t where not exists(select 1 from tb where customerid = t.customerid and (datetime > t.datetime or (datetime=t.datetime and id > t.id)))

drop table tb

/*
id          customerid  datetime                                               value       
----------- ----------- ------------------------------------------------------ ----------- 
2           1           2009-12-12 00:00:00.000                                4
4           2           2009-12-14 00:00:00.000                                2

（所影响的行数为 2 行）
*/

[dawugui]

提醒楼主，2009-14-14这个日期有错。

我在七楼改了你的测试数据.

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-23 15:09:12
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([id] int,[customerid] int,[datetime] varchar(10),[value] int)
insert [tb]
select 1,1,'2009-12-12',3 union all
select 2,1,'2009-12-12',4 union all
select 3,2,'2009-12-13',1 union all
select 4,2,'2009-14-14',2
--------------开始查询--------------------------
select 
* 
from
 tb t 
where
 not exists(select 1 from tb where customerid = t.customerid and (datetime > t.datetime or (datetime=t.datetime and id > t.id)))
----------------结果----------------------------
/* id          customerid  datetime   value
----------- ----------- ---------- -----------
2           1           2009-12-12 4
4           2           2009-14-14 2

(2 行受影响)
*/

[smntbk]

或者(SQL2005)

select id,customerid,[datetime],[value] from 
(select *,row_number() over(partition by customerid order by datetime,value desc) as rn from tb) p
where rn = 1
/*
id          customerid  datetime                value
----------- ----------- ----------------------- -----------
2           1           2009-12-12 00:00:00.000 4
3           2           2009-12-13 00:00:00.000 1

(2 行受影响)
*/

[smntbk]

select id,customerid,[datetime],[value] from 
(select *,row_number() over(partition by customerid order by datetime desc,value desc) as rn from tb) p
where rn = 1
/*
id          customerid  datetime                value
----------- ----------- ----------------------- -----------
2           1           2009-12-12 00:00:00.000 4
3           2           2009-12-14 00:00:00.000 2

(2 行受影响)
*/

[yangjia921]

感谢(爱新觉罗.毓华)，在我回复1，2楼的时候就给出了正确答案。

也有人给出了sql2005版本的sql，不知道对不对，有时间在去试，

[yangjia921]

（smntbk)写的sql2005版本也是对的，有空去看看

[csuyuanweiqingking]

这个貌似很有用