急 高手快来帮下我啊

[gameover200]

如:
表A 字段 A B
1 10
1 12
2 12
2 15
3 11
3 16
表B 字段 A B
1 数学
2 语文
3 英语
要求算没科的平均值,输出效果如下:
字段 数学 语文 英语
11 13.5 13.5

[gameover200]

自己顶下

[xeqtr1982]

declare @t table(A int,B dec(5,1))
insert into @t select 1,10
union all select 1 ,12
union all select 2 ,12
union all select 2 ,15
union all select 3 ,11
union all select 3 ,16

declare @a table(A int,B varchar(10))
insert into @a select 1 , '数学'
union all select 2 , '语文'
union all select 3 , '英语'

select a.A as A1,b.a as A2,a.B as B1,b.B as B2 into # from @t a,@a b where a.a=b.a
select distinct [数学]=cast((select avg(B1) from # where B2='数学'group by B2) as dec(5,1)),
[语文]=cast((select avg(B1) from # where B2='语文'group by B2) as dec(5,1)),
[英语]=cast((select avg(B1) from # where B2='英语'group by B2) as dec(5,1))
from #

drop table #

[撸大湿]

create table 表A(A int ,B float)
insert into 表A
select 1 , 10
union all select 1, 12
union all select 2, 12
union all select 2, 15
union all select 3, 11
union all select 3, 16

create table 表B(A int ,B nvarchar(100))
insert into 表B
select 1, '数学'
union all select 2, '语文'
union all select 3, '英语'

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',( select avg(B) from 表A where A='+cast(A as nvarchar(10))
+') as '''+B+'''' from 表B
select @sql='select '+stuff(@sql,1,1,'')
exec(@sql )
drop table 表A
drop table 表B



结果

数学 语文 英语
11 13.5 13.5

[yuweiwei]

create table AA(A int ,B int)
insert into AA select 1, 10
insert into AA select 1, 12
insert into AA select 2, 12
insert into AA select 2, 15
insert into AA select 3, 11
insert into AA select 3, 16

create table BB (A int,name varchar(20))
insert into BB select 1 , '数学'
insert into BB select 2 , '语文'
insert into BB select 3 , '英语'

declare @sql varchar(1000)
set @sql='select '
select @sql=@sql+'sum(case when tt.name='''+ttt.name +''' then avg else 0 end )as '+ttt.name +',' from
(select name from BB )ttt
set @sql=left(@sql,len(@sql)-1)+' from (select BB.name,tt.avg from (select A,sum(B)/count(*) avg from AA group by A)tt,BB where BB.A=tt.A)tt'
print @sql
exec (@sql)

[gameover200]

各位有简单的方法吗??就是看起来 写的没那么复杂

[sxdoujg]

select (select avg(b) from a as aa where aa.a = a.a and b.b='数学') 数学,
(select avg(b) from a as aa where aa.a = a.a and b.b='语文') 语文,
(select avg(b) from a as aa where aa.a = a.a and b.b='英语') 英语
from a

[gameover200]

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',( select avg(B) from 表A where A='+cast(A as nvarchar(10))
+') as '''+B+'''' from 表B
select @sql='select '+stuff(@sql,1,1,'')
exec(@sql )
那位大虾能给解释下 这句SQL语句啊

[gameover200]

主要是A='+cast(A as nvarchar(10))
和 as '''+B+'''' from 表B
我不太明白

[gameover200]

自己在顶下

[为无味]

declare @t table(A int,B float)
insert into @t select 1,10
union all select 1 ,12
union all select 2 ,12
union all select 2 ,15
union all select 3 ,11
union all select 3 ,16

declare @a table(A int,B varchar(10))
insert into @a select 1 , '数学'
union all select 2 , '语文'
union all select 3 , '英语'
select avg(t.B) AS G,a.B from @a a
left join @t t on a.A = t.A
GROUP BY a.B

[liangpei2008]

create table a (A int,B decimal)
insert into a
select 1,10
union all
select 1,12
union all
select 2,12
union all
select 2,15
union all
select 3,11
union all
select 3,16

create table b (A int,b varchar(10))
insert into b
select 1,'数学'
union all
select 2,'语文'
union all
select 3,'英语'
go
select '数学'=sum(case 科目 when '数学' then 成绩 else 0 end),'语文'=sum(case 科目 when '语文' then 成绩 else 0 end),
'英语'=sum(case 科目 when '英语' then 成绩 else 0 end)
from
(
select bb.b as '科目',aa.成绩 from b bb,
(select a.A,avg(a.B) as '成绩' from a
group by A) aa
where bb.A=aa.A

) cc

[gameover200]

在顶下 高手快来啊

[gameover200]

在顶下