sql2000如何查询这样的语句
A表 ID userName B表 ID AID Contacts addtime
1 zhangsan 1 1 第一次 2013-3-22
2 lisi 2 1 第二次 2013-3-23
3 wangwu 3 2 第一次 2013-3-23


结果显示： ID userName count contacts addtime
1 zhangsan 2 第二次 2013-3-23
2 lisi 1 第一次 2013-3-23
3 wangwu 0 Null Null

汇总用户在B表的记录数，并拿到最后一次的信息。数据会很多 sql
[解决办法]
select id,count(AID),max(addtime) group by id

a表自己关联吧
[解决办法]
select a.id,a.username,(select count(AID), from B b where b.AID=a.ID) AS COUNTS,top 1 c.conut,c.Contacts,c.addtime left join B C where c.AID=a.ID order by ID DESC
[解决办法]
参考这个：

create table A
(
ID        int,
UserName  varchar(100)
)

create table B
(
ID        int,
AID       int,
Contacts  varchar(100),
addtime   datetime
)

insert into A values(1,'zhangsan')
insert into A values(2,'lisi')
insert into A values(3,'wangwu')

insert into B values(1,1,'第一次','2012-03-22')
insert into B values(2,1,'第二次','2012-03-23')
insert into B values(3,2,'第一次','2012-03-23')

select A.*,t.cnt,t.Contacts,t.addtime
 from A
left join
(
select *,(select COUNT(1) from B b2 where b2.AID = a.AID) as cnt
 from B a 
where ID in(select MAX(ID) from B b group by AID)
) as t
on A.ID = t.AID

IDUserNamecntContactsaddtime
1zhangsan2第二次2012-03-23 00:00:00.000

2lisi1第一次2012-03-23 00:00:00.000
3wangwuNULLNULLNULL
[解决办法]
select a.id,a.username,(select count(AID) from B b where b.AID=a.ID) AS COUNTS,top 1 c.conut,c.Contacts,c.addtime left join B C where c.AID=a.ID order by ID DESC

左连接 B b ,其中b是B的别名
[解决办法]
表自己关联~~~