create table TE(A varchar(20), B int, C datetime, D varchar(20)) insert TE select 'D111A', 2, '2006-5-23 23:12:10', 'cad1' union all select 'D111A', 3, '2006-5-24 10:32:55', 'cad1' union all select 'D111A', 2, '2006-5-24 13:52:43', 'cad1' union all select 'D222A', 2, '2006-5-25 12:42:34', 'cad1' union all select 'D222A', 2, '2006-5-25 13:23:43', 'cad1' union all select 'D222A', 3, '2006-5-26 14:12:32', 'cad1' union all select 'D333A', 2, '2006-5-27 12:43:12', 'cad1' union all select 'D333A', 2, '2006-5-27 23:12:22', 'cad1'select * from TE as tmp where not exists(select 1 from TE where A=tmp.A and C>tmp.C)--result A B C D -------------------- ----------- ------------------------------------------------------ -------------------- D111A 2 2006-05-24 13:52:43.000 cad1 D222A 3 2006-05-26 14:12:32.000 cad1 D333A 2 2006-05-27 23:12:22.000 cad1(3 row(s) affected)
create table t (A varchar(20), B numeric(20), C datetime, D varchar (20) )select * from t insert into t(a,b,c,d) select 'D111A' ,2,'2006-5-23 23:12:10', 'cad1' union all select 'D111A' ,3,'2006-5-24 10:32:55', 'cad1' union all select 'D111A' , 2 ,'2006-5-24 13:52:43', 'cad1' union all select 'D222A' , 2 ,'2006-5-25 12:42:34', 'cad1' union all select 'D222A' , 2 ,'2006-5-25 13:23:43', 'cad1' union all select 'D222A' , 3 ,'2006-5-26 14:12:32', 'cad1' union all select 'D333A' , 2 ,'2006-5-27 12:43:12', 'cad1' union all select 'D333A', 2 ,'2006-5-27 23:12:22', 'cad1'select * from t where exists(select max(t1.c) from t t1 group by a having t.c = max(t1.c)) A B C D -------------------- ---------------------- ------------------------------------------------------ -------------------- D111A 2 2006-05-24 13:52:43.000 cad1 D222A 3 2006-05-26 14:12:32.000 cad1 D333A 2 2006-05-27 23:12:22.000 cad1(所影响的行数为 3 行)
少加了一个条件 select * from t where exists(select max(t1.c) from t t1 group by a having t.c = max(t1.c) and t.a = t1.a)
Select TE.A,B,TE.C,D From TE join (Select A,Max(C) as C From TE Group By A) Temp on TE.C = Temp.C
select a.A,a.B,b.C,a.d from tb a,(select A,max(C) C from tb group by A ) b where a.A=b.A and a.C=b.C
两种方法 一. Select * from TE tr where C > ALL(SELECT C FROM TE where tr.A=TE.A AND tr.C<>TE.C)二. select * from TE WHERE C IN (SELECT MAX(C) FROM TE GROUP BY A)
版主的 Select * from TE,TE as TF where TE.A=TF.A and TE.C>TF.C 查出的结果是某条记录C字段值存在大于其它记录C字段值,而不是最大时间的
create table TE(A varchar(20), B int, C datetime, D varchar(20))
insert TE select 'D111A', 2, '2006-5-23 23:12:10', 'cad1'
union all select 'D111A', 3, '2006-5-24 10:32:55', 'cad1'
union all select 'D111A', 2, '2006-5-24 13:52:43', 'cad1'
union all select 'D222A', 2, '2006-5-25 12:42:34', 'cad1'
union all select 'D222A', 2, '2006-5-25 13:23:43', 'cad1'
union all select 'D222A', 3, '2006-5-26 14:12:32', 'cad1'
union all select 'D333A', 2, '2006-5-27 12:43:12', 'cad1'
union all select 'D333A', 2, '2006-5-27 23:12:22', 'cad1'select * from TE as tmp
where not exists(select 1 from TE where A=tmp.A and C>tmp.C)--result
A B C D
-------------------- ----------- ------------------------------------------------------ --------------------
D111A 2 2006-05-24 13:52:43.000 cad1
D222A 3 2006-05-26 14:12:32.000 cad1
D333A 2 2006-05-27 23:12:22.000 cad1(3 row(s) affected)
(A varchar(20),
B numeric(20),
C datetime,
D varchar (20)
)select * from t
insert into t(a,b,c,d)
select 'D111A' ,2,'2006-5-23 23:12:10', 'cad1' union all
select 'D111A' ,3,'2006-5-24 10:32:55', 'cad1' union all
select 'D111A' , 2 ,'2006-5-24 13:52:43', 'cad1' union all
select 'D222A' , 2 ,'2006-5-25 12:42:34', 'cad1' union all
select 'D222A' , 2 ,'2006-5-25 13:23:43', 'cad1' union all
select 'D222A' , 3 ,'2006-5-26 14:12:32', 'cad1' union all
select 'D333A' , 2 ,'2006-5-27 12:43:12', 'cad1' union all
select 'D333A', 2 ,'2006-5-27 23:12:22', 'cad1'select * from t
where exists(select max(t1.c) from t t1 group by a having t.c = max(t1.c))
A B C D
-------------------- ---------------------- ------------------------------------------------------ --------------------
D111A 2 2006-05-24 13:52:43.000 cad1
D222A 3 2006-05-26 14:12:32.000 cad1
D333A 2 2006-05-27 23:12:22.000 cad1(所影响的行数为 3 行)
select * from t
where exists(select max(t1.c) from t t1 group by a having t.c = max(t1.c) and t.a = t1.a)
(Select A,Max(C) as C From TE Group By A) Temp
on TE.C = Temp.C
一. Select * from TE tr
where C > ALL(SELECT C FROM TE where tr.A=TE.A AND tr.C<>TE.C)二. select * from TE WHERE C IN (SELECT MAX(C) FROM TE GROUP BY A)
Select *
from TE,TE as TF
where TE.A=TF.A and TE.C>TF.C
查出的结果是某条记录C字段值存在大于其它记录C字段值,而不是最大时间的