有表1
字段:ID Int(自动增加),A Char(6),B Char(4),C DateTime
A、B字段值可重复,不可为空
现要查询Max(C)所在行数据下面这条查询语句查出的A值不唯一,加DISTINCT也同样不起做用
Select A,B,Max(C) From 表1
Group By A,B怎么能够实现?
字段:ID Int(自动增加),A Char(6),B Char(4),C DateTime
A、B字段值可重复,不可为空
现要查询Max(C)所在行数据下面这条查询语句查出的A值不唯一,加DISTINCT也同样不起做用
Select A,B,Max(C) From 表1
Group By A,B怎么能够实现?
--这样?
select A,B,C from 表1 t where not exists(select 1 from 表1 where c>t.c)
1 001 T11 2008-3-10 20:15:16
2 002 T12 2008-3-11 16:10:36
3 001 T13 2008-3-12 18:10:10
想要得到的结果集如下:ID A B C
2 002 T12 2008-3-11 16:10:36
3 001 T13 2008-3-12 18:10:10
上面几位朋友结的方法都没得到想要的结果。
--2楼的不是可以吗?你试过了?select * from 表1 t where not exists(select 1 from 表1 where c>t.c)
where a.A=b.A and a.C=b.C
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ID int,A varchar(3),B varchar(3),C datetime)
insert into #T
select 1,'001','T11','2008-3-10 20:15:16' union all
select 2,'002','T12','2008-3-11 16:10:36' union all
select 3,'001','T13','2008-3-12 18:10:10'select * from #T a where C = (select max(C) from #T where A=a.A)/*
ID A B C
2 002 T12 2008-3-11 16:10:36
3 001 T13 2008-3-12 18:10:10
*/
create table #T (ID int,A varchar(3),B varchar(3),C datetime)
insert into #T
select 1,'001','T11','2008-3-10 20:15:16' union all
select 2,'002','T12','2008-3-11 16:10:36' union all
select 3,'001','T13','2008-3-12 18:10:10'
select A,B,max(C)
from #t a
where not exists(select 1 from #t where A=a.A and C>a.C)
group by A,Bif object_id('tempdb.dbo.#T') is not null drop table #T
---- ---- ------------------------------------------------------
002 T12 2008-03-11 16:10:36.000
001 T13 2008-03-12 18:10:10.000(2 件処理されました)
create table #T (ID int,A varchar(3),B varchar(3),C datetime)
insert into #T
select 1,'001','T11','2008-3-10 20:15:16' union all
select 2,'002','T12','2008-3-11 16:10:36' union all
select 3,'001','T13','2008-3-12 18:10:10'
--☆☆☆不明白楼主想要的☆☆☆
--取max(C)所在记录
select A,B,C
from #T a
where not exists(select 1 from #T where C>a.C)
--区别字段B取max(C)所在记录
select A,B,C
from #T a
where not exists(select 1 from #T where B=a.B and C>a.C)
if object_id('tempdb.dbo.#T') is not null drop table #T
(3 件処理されました)A B C
---- ---- ------------------------------------------------------
001 T13 2008-03-12 18:10:10.000(1 件処理されました)A B C
---- ---- ------------------------------------------------------
001 T11 2008-03-10 20:15:16.000
002 T12 2008-03-11 16:10:36.000
001 T13 2008-03-12 18:10:10.000(3 件処理されました)