日期:2014-05-18  浏览次数:20471 次

求平均的平均,再设置值
一个工程下有好多个点,每个点有多种测量方法可以得到值。

工程表
工程ID 工程的值
001

工程的值,想要工程下所有点的平均值


点的测量表

主键 同一个点的唯一标识 测量方法 数值 所在工程ID
1 A AAA 1 001
2 A BBB 2 001
3 B CCC 199 001
4 B AAA 200 001
5 B BBB 201 001

之前有大侠给出过直接求平均,并赋值的办法

不过再根据那样做的话,如果要求工程001的值,得到的就是这5个数加起来除以5。但我现在希望的是
A点的两个值作平均,作为A点的真实值,B点的3个值作平均,作为B点的真实值。
也就是工程001的值想要 ((1+2)/2+(199+200+201)/3)/2,而不是(1+2+199+200+201)/5



之前有个求平均,再设置值
有兴趣的可以看一下。


其实,之前根据大侠给出的办法,已经能得到这样的一张表

主键 同一个点的唯一标识 测量方法 数值 平均值 所在工程ID
1 A AAA 1 1.5 001
2 A BBB 2 1.5 001
3 B CCC 199 200 001
4 B AAA 200 200 001
5 B BBB 201 200 001
我是怕大家看的困惑,所以前面没提


------解决方案--------------------
SQL code

create table ta(工程ID varchar(32),工程的值 int)
go
insert ta select '001',0
go
create table tb(主键 int identity(1,1), 同一个点的唯一标识 varchar(32), 测量方法 varchar(120), 数值 int, 所在工程ID varchar(32))
go
insert tb  
select 'A', 'AAA', 1,'001' union all
select 'A','BBB',2,'001' union all
select 'B','CCC',199,'001' union all
select 'B','AAA',200,'001' union all
select 'B','BBB',201,'001'

    
    update ta  set 工程的值=c.平均值  from
    (
    select avg(平均值)平均值 from (select a.工程ID,b.同一个点的唯一标识,avg(b.数值)平均值 from ta a join tb b on a.工程ID=b.所在工程ID
    group by a.工程ID,b.同一个点的唯一标识)aa where 工程ID=aa.工程ID)c

------解决方案--------------------
SQL code
select 工程ID, avg(avg_value) as avgValue
from (
    select 工程ID, 点的唯一标识, avg(数值) as avg_value
    from 点的测量表
    group by 工程ID, 点的唯一标识
) as a
group by 工程ID

------解决方案--------------------
SQL code

--> 测试数据:[TEST]
if object_id('[TEST]') is not null drop table [TEST]
create table [TEST]([工程ID] varchar(3),[工程的值] sql_variant)
insert [TEST]
select '001',null
--> 测试数据:[DETAILS]
if object_id('[DETAILS]') is not null drop table [DETAILS]
create table [DETAILS]([主键] int,[同一个点的唯一标识] varchar(1),[测量方法] varchar(3),[数值] int,[工程ID] varchar(3))
insert [DETAILS]
select 1,'A','AAA',1,'001' union all
select 2,'A','BBB',2,'001' union all
select 3,'B','CCC',199,'001' union all
select 4,'B','AAA',200,'001' union all
select 5,'B','BBB',201,'001'



UPDATE [TEST] 
SET [工程的值]=A.[数值] FROM(
SELECT [工程ID],SUM([数值])[数值]
FROM(
SELECT 
[同一个点的唯一标识],[工程ID],AVG(CAST([数值] AS FLOAT)) AS [数值]
FROM [DETAILS]
GROUP BY 
[同一个点的唯一标识],[工程ID])T GROUP BY [工程ID])A
WHERE A.工程ID=TEST.工程ID

SELECT * FROM TEST
/*
工程ID    工程的值
001    201.5
*/

------解决方案--------------------
SQL code

    update ta  set 工程的值= case when c.平均值 is null then 工程的值 else c.平均值 end from
    (
    select avg(平均值)平均值 from (select a.工程ID,b.同一个点的唯一标识,avg(b.数值)平均值 from ta a join tb b on a.工程ID=b.所在工程ID
    group by a.工程ID,b.同一个点的唯一标识)aa where 工程ID=aa.工程ID)c

------解决方案--------------------
SQL code

create table ta(工程ID varchar(32),工程的值 int)
go
insert ta select '001',0
go
select * from ta 
/*
工程ID    工程的值
001    0*/
create table tb(主键 int identity(1,1), 同一个点的唯一标识 varchar(32), 测量方法 varchar(120), 数值 int, 所在工程ID varchar(32))
go
insert tb  
select 'A', 'AAA', 1,'001' union all
select 'A','BBB',2,'001' union all
select 'B','CCC',199,'001' union all
select 'B','AAA',200,'001' union all
select 'B','BBB',201,'001'
select * from tb
/*
主键    同一个点的唯一标识    测量方法    数值    所在工程ID
1    A    AAA    1    001
2    A    BBB    2    001
3    B    CCC    199    001
4    B    AAA    200    001
5    B    BBB    201    001
*/
    
    
    update ta  set 工程的值= case when c.平均值