急,请大家帮个忙,关于两个关联表的统计
a 表
Ad_ID start_Date
1 2007-5-2 10:38:05
2 2007-5-1 20:27:36
b 表
IDAd_IDAd_SC_Date
112007-4-30 18:27:36
212007-5-1 18:27:36
322007-5-1 18:38:05
412007-5-2 18:38:05
512007-5-2 18:40:45
622007-5-3 18:40:45
如何统计a表中的Ad_ID在b表中出现的次数,并且统计的大于a表中start_Date的数据?
统计结果如下:
Ad_ID count
1 2
2 1
[解决办法]
select Ad_ID,(select count(*) from b where Ad_ID=a.Ad_ID and b.Ad_SC_Date> a.start_Date) as [count]
from a
[解决办法]
select
Ad_ID,
(select count(*) from b where Ad_ID=T.Ad_ID and Ad_SC_Date> T.start_Date) as [count]
from a AS T
