SELECT语句如何用一条语句实现在对部分列SUM的基础上再对其它列进行AVG
示例如下,有一个表,主键有三列,对其中一列SUM,一列AVG,一列显示:
CREATE TABLE [dbo].[u_team] (
[id_1] varchar(16) NOT NULL,
[id_avg] varchar(16) NOT NULL,
[id_sum] varchar(16) NOT NULL,
[the_sum] [numeric](18,2) NULL,
PRIMARY KEY (id_1, id_avg, id_sum)
) ON [PRIMARY]
GO
正常需要这样实现
SELECT id_1, AVG(the_sum) AS the_sum
FROM (SELECT id_1, id_avg, SUM(the_sum) AS the_sum FROM u_team
GROUP BY id_1, id_avg) v_temp
GROUP BY id_1
但因为多次用到这类查询语句,所以最好是能只用一次SELECT的语句来实现(建视图更不可取),列的语法复杂无所谓,只要不需两次SELECT就成。
各位有没有更好的方法?
[解决办法]
select id_1, sum(the_sum)/count(id_1) from u_team group by id_1
[解决办法]
--try
select id_1, the_sum=sum(the_sum)/count(distinct id_1+id_avg)
from u_team
group by id_1
[解决办法]
select id_1, sum(the_sum)/count(id_avg) from u_team group by id_1
[解决办法]
declare @table table
(
id int,
id_1 int,
i_avg int,
i_sum int
)
insert into @table select 1,1,1,14
insert into @table select 1,1,1,14
insert into @table select 2,1,2,15
insert into @table select 2,1,2,15
insert into @table select 3,1,3,16
insert into @table select 3,1,3,16
insert into @table select 4,2,1,17
insert into @table select 5,2,2,18
insert into @table select 6,2,3,19
insert into @table select 7,3,1,10
insert into @table select 8,3,2,11
insert into @table select 9,3,3,12
insert into @table select 10,4,1,13
insert into @table select 11,4,2,100
--楼主的语句
select id_1,avg(i_sum)
from
(select id_1,i_avg,sum(i_sum) as i_sum from @table group by id_1,i_avg
)tt
group by id_1
--结果
130
218
311
456
--更改后的
select id_1,sum(i_sum) / count(distinct i_avg) as i_sum
from @table
group by id_1
--结果
130
218
311
456