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 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
group by a.mid,b.sums
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) as num from @a M
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
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
select b.sums as 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
group by a.mid,b.sums