A 表
ID, TITLE
1 T1
2 T2
3 T3 B 表 -- A_id 为A表中的ID
ID A_id NOte Author Bdate
1 1 AAA 张三 2001-01-01
2 2 BBB 李四 2002-01-01
3 3 CCC 王五 2003-01-01
4 1 AAA 张三 2004-01-01
5 2 BBB 李四 2002-01-01
6 3 CCC 王五 2003-01-01求出B表中对应一A表最大的ID记录,这样效率好像不是很高,求更好一点的方法。Select A.ID,
A.Title,
(select top 1 NOte from B where A_id=A.ID Order by ID DESC) AS note,
(select top 1 Author from B where A_id=A.ID Order by ID DESC) AS Author,
(select top 1 Bdate from B where A_id=A.ID Order by ID DESC) AS Bdate
From A
ID, TITLE
1 T1
2 T2
3 T3 B 表 -- A_id 为A表中的ID
ID A_id NOte Author Bdate
1 1 AAA 张三 2001-01-01
2 2 BBB 李四 2002-01-01
3 3 CCC 王五 2003-01-01
4 1 AAA 张三 2004-01-01
5 2 BBB 李四 2002-01-01
6 3 CCC 王五 2003-01-01求出B表中对应一A表最大的ID记录,这样效率好像不是很高,求更好一点的方法。Select A.ID,
A.Title,
(select top 1 NOte from B where A_id=A.ID Order by ID DESC) AS note,
(select top 1 Author from B where A_id=A.ID Order by ID DESC) AS Author,
(select top 1 Bdate from B where A_id=A.ID Order by ID DESC) AS Bdate
From A
FROM A
JOIN B ON A.ID=B.A_ID
AND NOT EXISTS(SELECT 1 FROM B T WHERE T.A_ID=B.A_ID AND T.ID>B.ID)
if object_id('[A]') is not null drop table [A]
go
create table [A]([ID] int,[TITLE] varchar(2))
insert [A]
select 1,'T1' union all
select 2,'T2' union all
select 3,'T3'
if object_id('[B]') is not null drop table [B]
go
create table [B]([ID] int,[A_id] int,[NOte] varchar(3),[Author] varchar(4),[Bdate] datetime)
insert [B]
select 1,1,'AAA','张三','2001-01-01' union all
select 2,2,'BBB','李四','2002-01-01' union all
select 3,3,'CCC','王五','2003-01-01' union all
select 4,1,'AAA','张三','2004-01-01' union all
select 5,2,'BBB','李四','2002-01-01' union all
select 6,3,'CCC','王五','2003-01-01'
---查询---
SELECT A.*,B.NOte,B.Author,B.Bdate
FROM A
JOIN B ON A.ID=B.A_ID
AND NOT EXISTS(SELECT 1 FROM B T WHERE T.A_ID=B.A_ID AND T.ID>B.ID)---结果---
ID TITLE NOte Author Bdate
----------- ----- ---- ------ ------------------------------------------------------
1 T1 AAA 张三 2004-01-01 00:00:00.000
2 T2 BBB 李四 2002-01-01 00:00:00.000
3 T3 CCC 王五 2003-01-01 00:00:00.000(所影响的行数为 3 行)
AND NOT EXISTS(SELECT 1 FROM B T WHERE T.A_ID=B.A_ID AND T.ID>B.ID)
http://topic.csdn.net/u/20100205/10/634caeb2-8db4-4b57-b950-d5d0ab09279c.html