sql语句，查出每组中时间最近的一条记录

[sigridl]

fenquID bkID ID vdatetime(时间)
1 1 1 2009-12-02 10:50:45
1 1 2 2009-12-02 11:50:45
1 2 1 2009-12-01 10:50:45

一个fenquID 包括好几个bkID,一个bkID包括好几个ID

[sgtzzc]

以哪个来分组？

[让你望见影子的墙]

select fenquID , bkID ,   ID   , vdatetime=max(vdatetime)
from tb
group by fenquID , bkID  ,  ID    

[--小F--]

select 
 *
from
 tb t
where
 vdatetime=(select max(vdatetime) from tb where id=t.id)

[快乐_石头]

select * from tb t 
where not exists(select 1 from tb where fenquID=t.fenquID and bkID=t.bkID
and vdatetime>t.vdatetime)

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-09 13:26:45
-- 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]([fenquID] int,[bkID] int,[ID] int,[vdatetime] datetime)
insert [tb]
select 1,1,1,'2009-12-02 10:50:45' union all
select 1,1,2,'2009-12-02 11:50:45' union all
select 1,2,1,'2009-12-01 10:50:45'
--------------开始查询--------------------------
select 
 *
from
 tb t
where
 vdatetime=(select max(vdatetime) from tb where id=t.id)
----------------结果----------------------------
/* fenquID     bkID        ID          vdatetime
----------- ----------- ----------- -----------------------
1           1           2           2009-12-02 11:50:45.000
1           1           1           2009-12-02 10:50:45.000

(2 行受影响)
*/

[sgtzzc]

SELECT * 
FROM TB T
WHERE NOT EXISTS(SELECT 1 FROM TB WHERE bkID=T.bkID AND vdatetime>T.vdatetime)

如果以bkID来分组

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-09 13:26:45
-- 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]([fenquID] int,[bkID] int,[ID] int,[vdatetime] datetime)
insert [tb]
select 1,1,1,'2009-12-02 10:50:45' union all
select 1,1,2,'2009-12-02 11:50:45' union all
select 1,2,1,'2009-12-01 10:50:45'
--------------开始查询--------------------------
select 
 *
from
 tb t
where
 vdatetime=(select max(vdatetime) from tb where bkID=t.bkID)
----------------结果----------------------------
/* fenquID     bkID        ID          vdatetime
----------- ----------- ----------- -----------------------
1           2           1           2009-12-01 10:50:45.000
1           1           2           2009-12-02 11:50:45.000

(2 行受影响)
*/

[--小F--]

---还是这样?
----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-09 13:26:45
-- 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]([fenquID] int,[bkID] int,[ID] int,[vdatetime] datetime)
insert [tb]
select 1,1,1,'2009-12-02 10:50:45' union all
select 1,1,2,'2009-12-02 11:50:45' union all
select 1,2,1,'2009-12-01 10:50:45'
--------------开始查询--------------------------
select 
 *
from
 tb t
where
 vdatetime=(select max(vdatetime) from tb where fenquID=t.fenquID and bkID=t.bkID)
----------------结果----------------------------
/* fenquID     bkID        ID          vdatetime
----------- ----------- ----------- -----------------------
1           2           1           2009-12-01 10:50:45.000
1           1           2           2009-12-02 11:50:45.000

(2 行受影响)
*/

[快乐_石头]

[bobge]

每组(fenqu)的最新记录
select t.*
from table as t left join (
select fenquID max(vdatetime)
group by fenquID
) as t2 on t.fenquID = t2.fenquID and t.vdatetime = t2.vdatetime

[sigridl]

[Quote=引用 8 楼 fredrickhu 的回复:]
SQL code---还是这样?
----------------------------------------------------------------
-- Author :fredrickhu(我是小F，向高手学习)
-- Date :2009-12-09 13:26:45
-- Version:
-- Microsoft SQL Server 2005 - 9.0?-
[/Quote]

我想要的就是这种，谢谢你！
我还想问一下，能不能同时查出每组中的记录条数？

[qurihong]

1. select * from table_test where vdatetime in (select max(vdatetime) from table_test group by fenquID)

2. select * from table_test as a where vdatetime = ( select max(vdatetime) from table_test where fenquID=a.fenquID)

[Rotel-刘志东]

select * from  table t
where  vdatetime=(select max(vdatetime) from table where id=t.id)

[qurihong]

[Quote=引用 11 楼 sigridl 的回复:]
引用 8 楼 fredrickhu 的回复:
SQL code---还是这样?
----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-09 13:26:45
-- Version:
--      Microsoft SQL Server 2005 - 9.0?-


我想要的就是这种，谢谢你！
我还想问一下，能不能同时查出每组中的记录条数？
[/Quote]

select *,(select count(*) from table_test where fenquID=a.fenquID) as record_num from table_test as a where vdatetime = ( select max(vdatetime) from table_test where fenquID=a.fenquID)

[athloner]

8楼正解！