27,579
社区成员
发帖
与我相关
我的任务
分享
set nocount on
create table ta(工程ID varchar(32),工程的值 numeric(10,2))
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'
go
update ta set 工程的值 = avgValue
from ta
join (
select 所在工程ID, avg(avg_value) as avgValue
from (
select 所在工程ID, 同一个点的唯一标识, avg(数值*1.) as avg_value
from tb
group by 所在工程ID, 同一个点的唯一标识
) as a
group by 所在工程ID
) as b on 所在工程ID=工程ID
go
select * from ta
go
drop table tb, ta
go
-- 工程ID 工程的值
-- 001 100.75
--
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'
go
select 所在工程ID, avg(avg_value) as avgValue
from (
select 所在工程ID, 同一个点的唯一标识, avg(数值*1.) as avg_value
from tb
group by 所在工程ID, 同一个点的唯一标识
) as a
group by 所在工程ID
go
drop table tb, ta
go
--
-- (所影响的行数为 1 行)
--
--
-- (所影响的行数为 5 行)
--
-- 所在工程ID avgValue
-- 001 100.750000
--
-- (所影响的行数为 1 行)
--
--如果表结构类似这样
create table ta(工程ID varchar(32),工程的值 money)
--下面是改动的地方
case when ceiling(c.平均值) is null then 工程的值 else ceiling(c.平均值) end
-----------------------
create table ta(工程ID varchar(32),工程的值 money)
go
insert ta select '001',0
go
select * from ta
/*
工程ID 工程的值
001 0.00
*/
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 ceiling(c.平均值) is null then 工程的值 else ceiling(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
select * from ta
/*
工程ID 工程的值
001 100.00
*/
drop table ta
drop table tb
--> 测试数据:[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 [工程的值]=m.[数值]
from(
select [工程ID],AVG([数值]) as [数值]
from(
select [同一个点的唯一标识],
[工程ID],
AVG(cast([数值] as float)) [数值]
from [DETAILS]
group by [同一个点的唯一标识],[工程ID])t
group by [工程ID])m
where m.工程ID=[TEST].工程ID
select * from test
/*
工程ID 工程的值
001 100.75
*/
--> 测试数据:[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 [工程的值]=m.[数值]
from(
select [工程ID],AVG([数值]) as [数值]
from(
select [同一个点的唯一标识],
[工程ID],
AVG(cast([数值] as float)) [数值]
from [DETAILS]
group by [同一个点的唯一标识],[工程ID])t
group by [工程ID])m
where m.工程ID=[TEST].工程ID
select * from test
/*
工程ID 工程的值
001 100.75
*/
尼玛,怎么总是有人手贱删我的回复
CREATE TABLE [工程表] ([工程ID] VARCHAR(10), [工程的值] INT)
CREATE TABLE [点的测量表] ([主键] INT, [同一个点的唯一标识] VARCHAR(5),
[测量方法] VARCHAR(10), [数值] INT, [所在工程ID] VARCHAR(10))
INSERT INTO [工程表]
SELECT '001',NULL
INSERT INTO [点的测量表]
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'
SELECT a.*,
[平均值]=(SELECT AVG(CAST([数值] AS NUMERIC(12,2))) FROM [点的测量表] WHERE [同一个点的唯一标识]=a.[同一个点的唯一标识] AND [所在工程ID]=a.[所在工程ID])
FROM [点的测量表] a
/*
主键 同一个点的唯一标识 测量方法 数值 所在工程ID 平均值
----------- --------- ---------- ----------- ---------- ---------------------------------------
1 A AAA 1 001 1.500000
2 A BBB 2 001 1.500000
3 B CCC 199 001 200.000000
4 B AAA 200 001 200.000000
5 B BBB 201 001 200.000000
(5 行受影响)
*/
UPDATE [工程表] SET [工程的值]=[平均值]
FROM
(
SELECT [所在工程ID],CAST(AVG([平均值]) AS NUMERIC(12,2)) AS [平均值] FROM
(
SELECT a.*,
[平均值]=(SELECT AVG(CAST([数值] AS NUMERIC(12,2))) FROM [点的测量表] WHERE [同一个点的唯一标识]=a.[同一个点的唯一标识] AND [所在工程ID]=a.[所在工程ID])
FROM [点的测量表] a
) t GROUP BY [所在工程ID]
) AS t1
WHERE [工程表].[工程ID]=t1.[所在工程ID]
SELECT * FROM [工程表]
/*工程ID 工程的值
---------- -----------
001 120
(1 行受影响)*/
DROP TABLE [工程表]
DROP TABLE [点的测量表]
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.平均值 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
select * from ta
/*
工程ID 工程的值
001 100
*/
drop table ta
drop table tb
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
--> 测试数据:[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
*/
select 工程ID, avg(avg_value) as avgValue
from (
select 工程ID, 点的唯一标识, avg(数值) as avg_value
from 点的测量表
group by 工程ID, 点的唯一标识
) as a
group by 工程ID
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