有一个点表A
主键 数值
1 123
点表B
主键 A表主键 测量方法 数值
999 1 AAA 122
1000 1 BBB 124
一个点，有唯一的"数值"，放在表A里，但由于有不同的测量方法，导致"数值"结果"不一样，这样，一种测量方法就会有一种数值。
现在，想做两个查询，
一个是，查询每个点的平均数值。
第二个是，能不能把查询出来的平均数值，设置到A表的"数值"字段。
select *, avg(b.数值) from B left join A on a.主键=b.A表主键
------解决方案--------------------
select *, avg(b.数值) from B left join A on a.主键=b.A表主键
group by a.主键
------解决方案--------------------
- SQL code
UPDATE a SET [数值]=d.[平均数值] from (select c.[主键],c.[平均数值] from (select a.[主键], avg(b.数值)[平均数值] from B left join A on a.[主键]=b.A表主键 group by a.[主键])c where a.[主键]=c.[主键])d
------解决方案--------------------
- SQL code
DECLARE @A TABLE(id INT ,VALUE INT)INSERT INTO @ASELECT 1,NULLDECLARE @B TABLE(id INT,A_id INT,method VARCHAR(10),VALUE INT )INSERT INTO @BSELECT 999, 1, 'AAA', 122 UNION ALLSELECT 1000,1, 'BBB', 124--1,SELECT [@A].id,AVG([@B].VALUE) AS avg_value FROM @A LEFT OUTER JOIN @B ON [@A].id = [@B].A_idGROUP BY [@A].id/*id avg_value----------- -----------1 123*/--2UPDATE @A SET VALUE=t.avg_valueFROM ( SELECT [@A].id,AVG([@B].VALUE) AS avg_value FROM @A LEFT OUTER JOIN @B ON [@A].id = [@B].A_id GROUP BY [@A].id) AS t WHERE [@A].id=t.idSELECT * FROM @A/*id VALUE----------- -----------1 123*/
------解决方案--------------------
- SQL code
UPDATE a SET [数值]= case when d.[平均数值] is not null then d.[平均数值] else a.[数值] end from (select c.[主键],c.[平均数值] from (select *, avg(b.数值)[平均数值] from B left join A on a.主键=b.A表主键 group by a.主键)c where a.主键=c.主键)d