求两张表中同一字段个数统计的视图？

[ccssme]

表a 表b:都有一个MID字段，要取出a和b表中相同MID的个数？？

[蓝天]

select a.mid from a, b where a.mid = b.mid

[蓝天]

select count(a.mid) from a, b where a.mid = b.mid

[bflovesnow]

create view v_ab
as
select count(a.id) num from a inner join b on a.mid=b.mid

[---涛声依旧---]

select count(a.mid) from a inner join b on a.mid=b.mid

[pengdali]

select count(distinct a.mid) from a inner join b on a.mid=b.mid

[ccssme]

我按 yujohny(踏网无痕) 的方法　，取出一张表如下
mid sum
1 5
2 3
我的要求是按表a.mid的值取出对应的sum ，要怎么写呢？

[zjcxc]

--按表A取
select a.mid,num=(select sum(1) from a where mid=aa.mid)+(select sum(1) from b where mid=aa.mid) from a aa

--按表B取
select b.mid,num=(select sum(1) from a where mid=aa.mid)+(select sum(1) from b where mid=aa.mid) from b aa

[zjcxc]

--上面的有点错:
--按表A取
select aa.mid,num=(select sum(1) from a where mid=aa.mid)+(select sum(1) from b where mid=aa.mid) from a aa

--按表B取
select aa.mid,num=(select sum(1) from a where mid=aa.mid)+(select sum(1) from b where mid=aa.mid) from b aa

[zjcxc]

--测试
declare @a table(mid int)
insert into @a select 1
insert into @a select 2
insert into @a select 1
insert into @a select 2

declare @b table (mid int)
insert into @b select 1
insert into @b select 1
insert into @b select 1
insert into @b select 2

--按表A取
select aa.mid,num=(select sum(1) from @a where mid=aa.mid)+(select sum(1) from @b where mid=aa.mid) from @a aa

--按表B取
select aa.mid,num=(select sum(1) from @a where mid=aa.mid)+(select sum(1) from @b where mid=aa.mid) from @b aa

/*--结果

--按表A取
mid num
----------- -----------
1 5
2 3
1 5
2 3

（所影响的行数为 4 行）

--按表B取
mid num
----------- -----------
1 5
1 5
1 5
2 3

（所影响的行数为 4 行）
--*/

[ccssme]

不对，不对，我的意思你们理解错了！
a.mid b.mid num
1 1 5
2 1 3
1 1 5
2 2 3
以A表为准，找出a.mid相同的值在a与b表中的个数

[yoki]

啥意思？没看懂啊
把你的表A 、B的结构贴出来

[ccssme]

表A：aID mid ...
表b：bID mid ...

[aierong]

create table a(mid int)
insert into a select 1
insert into a select 2
insert into a select 1
insert into a select 2

create table b(mid int)
insert into b select 1
insert into b select 1
insert into b select 1
insert into b select 2


select a.mid,b.sums
from a join (
select a.mid,a.sums+b.sums sums
from (
select mid,count(*) sums
from a
group by mid) a join (
select mid,count(*) sums
from b
group by mid) b on a.mid=b.mid
) b
on a.mid=b.mid

[aierong]

select a.mid,b.sums num
from a join (
select a.mid,a.sums+b.sums sums
from (
select mid,count(*) sums
from a
group by mid) a join (
select mid,count(*) sums
from b
group by mid) b on a.mid=b.mid
) b
on a.mid=b.mid

[yujohny]

select a.mid,count(*) num from a left join (select mid from a union all select mid from b) T on a.mid=T.mid
group by a.mid

[Dennis618]

declare @a table(mid int)
declare @b table(mid int)
insert into @a values(1)
insert into @a values(2)
insert into @a values(1)
insert into @a values(2)
insert into @b values(1)
insert into @b values(1)
insert into @b values(1)
insert into @b values(2)
select m.mid,(select count(*) from @a a where a.mid=m.mid)+(select count(*) from @b b where b.mid=m.mid) from @a M

[ccssme]

按yujohny(踏网无痕)的思路，我做出来了，谢谢各位