表A:aid aname
1 aaa
2 bbb
3 ccc表B:bid aid bstatus btime
1 1 2 2012-06-04
2 1 1 2012-06-05
3 2 1 2012-06-04
4 2 2 2012-06-05需求:A 和 B是一对多的关系,查询统计A表数据状态为1的数量(以最大时间为依据)
在线等 先谢谢各位大侠
1 aaa
2 bbb
3 ccc表B:bid aid bstatus btime
1 1 2 2012-06-04
2 1 1 2012-06-05
3 2 1 2012-06-04
4 2 2 2012-06-05需求:A 和 B是一对多的关系,查询统计A表数据状态为1的数量(以最大时间为依据)
在线等 先谢谢各位大侠
--问题本身有问题,怎么和时间有关系呢?
select a.aid,aname,b1.cou
from a,(select aid,count(1) cou
from b
where bstatus=1
group by b.aid) b1
where a.aid=b1.aid;
--是这个意思吧?
select a.aid,a.aname,b.bid,b.btime
from a,b
where a.aid=b.aid
and b.bstatus=1
and (a.aid,b.btime) in
(select b.aid,max(b.btime)
from b
group by b.aid);
select a.aid,
a.name,
count(b.aid) cnt,
max(b.btime) btime
from a,b
where a.aid = b.aid
and b.bstatus = 1
group by a.aid,a.name