一个表:Table,内容如下:ID Country 属性1 属性2 属性3
1 AAA A1 A2 A3
2 BBB B1 B2 B3
3 CCC C1 C2 C3
4 AAA A21 A22 A23
5 AAA A31 A32 A33
6 BBB B21 B22 B23
7 CCC C21 C22 C23
8 BBB B31 B32 B33
9 CCC C31 C32 C33找出 AAA BBB CCC 各自 最新的那一条记录,并显示相应的属性1,属性2,属性3
1 AAA A1 A2 A3
2 BBB B1 B2 B3
3 CCC C1 C2 C3
4 AAA A21 A22 A23
5 AAA A31 A32 A33
6 BBB B21 B22 B23
7 CCC C21 C22 C23
8 BBB B31 B32 B33
9 CCC C31 C32 C33找出 AAA BBB CCC 各自 最新的那一条记录,并显示相应的属性1,属性2,属性3
select * from tb a where not exists(select 1 from tb where country=a.country and id>a.id)
where id IN (
select max(id) from table where Country='AAA',
select max(id) from table where Country='BBB',
select max(id) from table where Country='CCC'
)
insert into tb select 1,'AAA','A1','A2','A3'
insert into tb select 2,'BBB','B1','B2','B3'
insert into tb select 3,'CCC','C1','C2','C3'
insert into tb select 4,'AAA','A21','A22','A23'
insert into tb select 5,'AAA','A31','A32','A33'
insert into tb select 6,'BBB','B21','B22','B23'
insert into tb select 7,'CCC','C21','C22','C23'
insert into tb select 8,'BBB','B31','B32','B33'
insert into tb select 9,'CCC','C31','C32','C33'
go
select * from tb a where not exists(select 1 from tb where country=a.country and id>a.id)
/*
ID Country 属性1 属性2 属性3
----------- ---------- ---------- ---------- ----------
5 AAA A31 A32 A33
8 BBB B31 B32 B33
9 CCC C31 C32 C33(3 行受影响)*/
go
drop table tb
NO!
group 也是可以的,但麻烦些.要在子程序里以country分组求最大ID.
select * from tb a where id in(select max(id) from tb where country=a.country)