表a
uid bid n1
1 1 10
1 2 30
1 3 20
2 1 10
2 2 10
表b
uid bid n1
1 4 11
1 5 25
1 6 34
2 5 10
希望得到的结果
uid avg count
1 20 6
2 10 3
也就是说,按id查出表a n1字段的平均值
以及表a和表b中的总记录数
uid bid n1
1 1 10
1 2 30
1 3 20
2 1 10
2 2 10
表b
uid bid n1
1 4 11
1 5 25
1 6 34
2 5 10
希望得到的结果
uid avg count
1 20 6
2 10 3
也就是说,按id查出表a n1字段的平均值
以及表a和表b中的总记录数
if object_id('tempdb.dbo.#表a') is not null drop table #表a
create table #表a (uid int,bid int,n1 int)
insert into #表a
select 1,1,10 union all
select 1,2,30 union all
select 1,3,20 union all
select 2,1,10 union all
select 2,2,10
--> 测试数据: #表b
if object_id('tempdb.dbo.#表b') is not null drop table #表b
create table #表b (uid int,bid int,n1 int)
insert into #表b
select 1,4,11 union all
select 1,5,25 union all
select 1,6,34 union all
select 2,5,10select a.uid,avg(a.n1) [avg],count(*) cnt
from #表a a
join #表b b
on a.uid=b.uid
group by a.uid/*
uid avg cnt
----------- ----------- -----------
1 20 9
2 10 2(2 行受影响)
*/
9........应该是6啊...3楼这个有问题的吧
这样就成了a.sum/(a.count+b.count)了..
1楼感觉效率不是很好吧....
insert into @a
select 1,1,10 union all
select 1,2,30 union all
select 1,3,20 union all
select 2,1,10 union all
select 2,2,10
declare @b table (uid int,bid int,n1 int)
insert into @b
select 1,4,11 union all
select 1,5,25 union all
select 1,6,34 union all
select 2,5,10select uid,[avg]=avg(n1),cnt=count(*) from (
select * from @a union all
select * from @b
)t group by uid
改造下:这样试试
select uid,sum(n1)/sum(cnt) as [avg],count(*) from (select *,1 as cnt from a union all select uid,bid,0,0 as cnt from b) group by uid
(
select uid,bid,n1 from a
union all select uid,bid,n1 from b ) a
group by uid
uid
不过LZ给的结果好象有错