求SQL在VIEW里面获得平均值的语句多谢-MSSQL教程-爱易网页

求SQL在VIEW里面获得平均值的语句多谢

日期：2014-05-18　浏览次数：20608 次

求SQL在VIEW里面获得平均值的语句谢谢

CREATE VIEW A

AS SELECT A, B, C
AVG (D)

from TableA TableB, TableC

where (xxxxxxxxxx)

。。。尝试用AVG 不能通过。显示 not a single-group group function。。大概思想就是比如在公司系统里面找到叫张三的人，他有12个月工资，VIEW要列出一条包括他的姓名，电话等信息最后就是平均工资（D）。。新手不知道要怎么弄，非常感谢

------解决方案--------------------

SQL code


create table TableA
(
   id int,
   name nvarchar(10),
   phone varchar(20)
   
)

create table TableB
(
   id int,
   wage int
   
)


insert into TableA 
select 1,'张三','123456789'

insert into TableB 
select 1,1000 union all
select 1,2000 union all
select 1,3000 union all
select 1,4000 union all
select 1,5000 union all
select 1,6000 union all
select 1,7000 union all
select 1,8000 union all
select 1,9000 union all
select 1,10000 union all
select 1,11000 union all
select 1,12000 

create view A
AS
select a.id ,a.name ,a.phone ,AVG (b.wage ) AS wage from TableA a,TableB b where a.id =b.id 
group by a.id ,a.name ,a.phone


select * from A