我有如下的表结构:
t1 t2 t3 t4 t5 t6
0001 0001 0001 22 2003-08-05 11:00:00.000,001 2003-08-05 11:00:00.000
0001 0001 0001 21 2003-08-05 12:00:00.000,001 2003-08-05 11:00:00.000
0001 0001 0001 10 2003-08-05 13:00:00.000,001 2003-08-05 13:00:00.000
0001 0002 0002 77 2003-08-05 14:00:00.000,001 2003-08-05 14:00:00.000
0001 0003 0003 88 2003-08-05 16:00:00.000,001 2003-08-05 16:00:00.000我想得到这样的结果:
0001 0001 0001 10 2003-08-05 13:00:00.000,001 2003-08-05 13:00:00.000
0001 0002 0002 77 2003-08-05 14:00:00.000,001 2003-08-05 14:00:00.000
0001 0003 0003 88 2003-08-05 16:00:00.000,001 2003-08-05 16:00:00.000也就是说只要是列:t1,t2,t3数据是一样的那么就取列t5的最大时间那一行。不知道如何写,请指教。
t1 t2 t3 t4 t5 t6
0001 0001 0001 22 2003-08-05 11:00:00.000,001 2003-08-05 11:00:00.000
0001 0001 0001 21 2003-08-05 12:00:00.000,001 2003-08-05 11:00:00.000
0001 0001 0001 10 2003-08-05 13:00:00.000,001 2003-08-05 13:00:00.000
0001 0002 0002 77 2003-08-05 14:00:00.000,001 2003-08-05 14:00:00.000
0001 0003 0003 88 2003-08-05 16:00:00.000,001 2003-08-05 16:00:00.000我想得到这样的结果:
0001 0001 0001 10 2003-08-05 13:00:00.000,001 2003-08-05 13:00:00.000
0001 0002 0002 77 2003-08-05 14:00:00.000,001 2003-08-05 14:00:00.000
0001 0003 0003 88 2003-08-05 16:00:00.000,001 2003-08-05 16:00:00.000也就是说只要是列:t1,t2,t3数据是一样的那么就取列t5的最大时间那一行。不知道如何写,请指教。
select z.*, x.t6
from (
select z.t1, z.t2, z.t3, [t5]=max(t5)
from 我有如下的表结构
group by z.t1, z.t2, z.t3
) z inner join 我有如下的表结构 x
on x.t1=z.t1 and x.t2=z.t2 and x.t3=z.t3 and x.t5=z.t5
from table1,
(select t1, t2, t3, t4, max(t5)
from table1
group by t1, t2, t3, t4) table2
where table1.t1 = table2.t1
and table1.t2 = table2.t2
and table1.t3 = table2.t3
and table1.t4 = table2.t4
//如果还不够精确加上
and table1.t5 = table2.max(t5)
where t5 =
(select max(t5) from table1 where t1 = A.t1 and t2 = A.t2 and t3 = A.t3)
create table table1(t1 char(4),t2 char(4),t3 char(4),t4 char(2), t5 datetime,t6 char(30))
insert into table1 values('0001','0001','0001','22','2003-08-05 11:00:00.000','001 2003-08-05 11:00:00.000')
insert into table1 values('0001','0001','0001','21','2003-08-05 12:00:00.000','001 2003-08-05 11:00:00.000')
insert into table1 values('0001','0001','0001','10','2003-08-05 13:00:00.000','001 2003-08-05 13:00:00.000')
insert into table1 values('0001','0002','0002','77','2003-08-05 14:00:00.000','001 2003-08-05 14:00:00.000')
insert into table1 values('0001','0003','0003','88','2003-08-05 16:00:00.000','001 2003-08-05 16:00:00.000')
SQL语句
select * from table1 A
where t5 in
(select max(t5) from table1 where t1 = A.t1 and t2 = A.t2 and t3 = A.t3)
结果集:
t1 t2 t3 t4 t5 t6
0001 0003 0003 88 2003-08-05 16:00:00.000 001 2003-08-05 16:00:00.000
0001 0002 0002 77 2003-08-05 14:00:00.000 001 2003-08-05 14:00:00.000
0001 0001 0001 10 2003-08-05 13:00:00.000 001 2003-08-05 13:00:00.000
where t5 in
(select max(t5) from table1 where t1 = A.t1 and t2 = A.t2 and t3 = A.t3)