create table a(mid int) insert into a select 1 insert into a select 2 insert into a select 1 insert into a select 2create 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 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
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
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
--按表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
--上面的有点错: --按表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
--测试 declare @a table(mid int) insert into @a select 1 insert into @a select 2 insert into @a select 1 insert into @a select 2declare @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 行) --*/
as
select count(a.id) num from a inner join b on a.mid=b.mid
a.mid b.mid num
1 1 5
2 1 3
1 1 5
2 2 3
以A表为准,找出a.mid相同的值在a与b表中的个数
表b:bID mid ...
insert into a select 1
insert into a select 2
insert into a select 1
insert into a select 2create 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
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
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
mid sum
1 5
2 3
我的要求是按表a.mid的值取出对应的sum ,要怎么写呢?
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
--按表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
declare @a table(mid int)
insert into @a select 1
insert into @a select 2
insert into @a select 1
insert into @a select 2declare @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 行)
--*/