数据库 A表: a1 a3
Q 10
Q 12
W 14
E 17
E 13 B表:b1 b2
Q MM
W NN我要查询得到的记录为:
MM Q 11
NN W 14上面的11为(10+12)/2=11(Q为多条记录,求平均值)有没好的办法,用一条SQL语句,就能得到结果?每条记录执行一次SQL,是否不好?
Q 10
Q 12
W 14
E 17
E 13 B表:b1 b2
Q MM
W NN我要查询得到的记录为:
MM Q 11
NN W 14上面的11为(10+12)/2=11(Q为多条记录,求平均值)有没好的办法,用一条SQL语句,就能得到结果?每条记录执行一次SQL,是否不好?
(SELECT A.A1,AVG(A3)AS A3 FROM A GROUP BY A1)AS A
,B WHERE A.A1=B.B1
from B inner join A on B.b1=A.A1
Group By B.b1
--> 测试数据: @A
declare @A table (a1 varchar(1),a3 int)
insert into @A
select 'Q',10 union all
select 'Q',12 union all
select 'W',14 union all
select 'E',17 union all
select 'E',13
--> 测试数据: @B
declare @B table (b1 varchar(1),b2 varchar(2))
insert into @B
select 'Q','MM' union all
select 'W','NN'select col1 = b.b2
,col2 = a.a1
,col3 = avg(a.a3)
from @A a
inner join @B b on a.a1 = b.b1
group by a.a1,b.b2col1 col2 col3
---- ---- -----------
MM Q 11
NN W 14(所影响的行数为 2 行)
where a.a1=b.b1
group by a.a1,b.b2
select b2 , a1 , avg(a3) as a3 from [B1] b
left join [a1] a on b.b1=a.a1
group by b2,a1
if object_id('[a1]') is not null drop table [a1]
create table [a1]([a1] varchar(1),[a3] int)
insert [a1]
select 'Q',10 union all
select 'Q',12 union all
select 'W',14 union all
select 'E',17 union all
select 'E',13--> 测试数据:[B1]
if object_id('[B1]') is not null drop table [B1]
create table [B1]([b1] varchar(1),[b2] varchar(2))
insert [B1]
select 'Q','MM' union all
select 'W','NN'
-------------------测试开始--------------------
select b2 , a1 , avg(a3) as a3 from [B1] b
left join [a1] a on b.b1=a.a1
group by b2,a1
---------------------结果----------------------
/*
b2 a1 a3
---- ---- -----------
MM Q 11
NN W 14(2 行受影响)
*/
goinsert into A
select 'Q', 10 union all
select 'Q', 12 union all
select 'W', 14 union all
select 'E', 17 union all
select 'E', 13
go
Create Table B(b1 nvarchar(20),b2 nvarchar(20))
go
insert into B
Select 'Q','MM' union all
select 'W','NN'
goselect B.b2,B.b1,avg(A.a3) a
from B inner join A on B.b1=A.A1
Group By B.b1,B.b2
/*
b2 b1 a
-------------------- -------------------- -----------
MM Q 11
NN W 14
*/
drop table A,B
insert into a
select 'Q', 10 union all
select 'Q', 12 union all
select 'W', 14 union all
select 'E', 17 union all
select 'E', 13;create table b(b1 varchar(2), b2 varchar(2));
insert into b
select 'Q', 'MM' union all
select 'W', 'NN';select b.b1, avg(a3) average from b left join a on a.a1=b.b1
group by b1;
insert into a
select 'Q', 10 union all
select 'Q', 12 union all
select 'W', 14 union all
select 'E', 17 union all
select 'E', 13;create table b(b1 varchar(2), b2 varchar(2));
insert into b
select 'Q', 'MM' union all
select 'W', 'NN';select b.b2, b.b1, avg(a3) average from b left join a on a.a1=b.b1
group by b.b2,b. b1;
declare @A table (a1 varchar(1),a3 int)
insert into @A
select 'Q',10 union all
select 'Q',12 union all
select 'W',14 union all
select 'E',17 union all
select 'E',13
--> 测试数据: @B
declare @B table (b1 varchar(1),b2 varchar(2))
insert into @B
select 'Q','MM' union all
select 'W','NN'
select b.b1,b.b2,a.av from @B as b inner join(select avg(a3)as av,a1 from @A group by a1)as a
on a.a1=b.b1
/*
b1 b2 av
Q MM 11
W NN 14
*/