有一表(aws) 字段 id time value1 value2 value3
值 1 12 1 1 1
1 13 2 2 2
1 5 3 3 3
2 14 1 1 1
2 23 2 2 2
3 16 1 1 1
3 02 2 2 2
3 21 3 3 3
我要从该表中分组查询出max(time)的各id的其他所有值
即 id time value1 value2 value3
1 13 2 2 2
2 23 2 2 2
3 21 3 3 3
请问SQL语言怎么写呢?
值 1 12 1 1 1
1 13 2 2 2
1 5 3 3 3
2 14 1 1 1
2 23 2 2 2
3 16 1 1 1
3 02 2 2 2
3 21 3 3 3
我要从该表中分组查询出max(time)的各id的其他所有值
即 id time value1 value2 value3
1 13 2 2 2
2 23 2 2 2
3 21 3 3 3
请问SQL语言怎么写呢?
A.*
From
aws A
Inner Join
(Select id, Max([time]) As [time] From aws Group By id) B
On A.id = B.id And A.[time] = B.[time]
(select id , max(time) as time from tb group by id) b
where a.id = b.id and a.time = b.time
Select
A.*
From
aws A
Where Not Exists (Select id From aws Where id = A.id And [time] < A.[time])
A.*
From
aws A
Where Not Exists (Select id From aws Where id = A.id And [time] > A.[time])
A.*
From
aws A
Where [time] In (Select Max([time]) From aws Where id = A.id)
drop table tb
gocreate table tb
(
id int,
time int,
value1 int,
value2 int,
value3 int
)insert into tb(id,time,value1,value2,value3) values(1, 12, 1, 1, 1)
insert into tb(id,time,value1,value2,value3) values(1, 13, 2, 2, 2)
insert into tb(id,time,value1,value2,value3) values(1, 5, 3, 3, 3)
insert into tb(id,time,value1,value2,value3) values(2, 14, 1, 1, 1)
insert into tb(id,time,value1,value2,value3) values(2, 23, 2, 2, 2)
insert into tb(id,time,value1,value2,value3) values(3, 16, 1, 1, 1)
insert into tb(id,time,value1,value2,value3) values(3, 02, 2, 2, 2)
insert into tb(id,time,value1,value2,value3) values(3, 21, 3, 3, 3)select a.* from tb a,
(select id , max(time) as time from tb group by id) b
where a.id = b.id and a.time = b.timedrop table tbid time value1 value2 value3
----------- ----------- ----------- ----------- -----------
3 21 3 3 3
2 23 2 2 2
1 13 2 2 2(所影响的行数为 3 行)