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
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的别名
[解决办法]
表自己关联~~~
