本文介绍了使用表行的SQL foreach的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个Access表.一个表(表1)具有唯一字段MyID,而另一个表(表2)具有具有MyID的几行,另一列为Data.我想写一条类似于foreach的SQL语句,其中从表2的数据中选择MyID的所有值并取平均值,然后在另一个字段Avg下的MyID行中进行更新.
I have two Access tables. One table (table1) has a unique field, MyID and another table (table2) has several rows with MyID and another column, Data. I'd like to write an SQL statement that is similar to a foreach where all the values for MyID are selected and averaged from table2's Data and then updated in the MyID row under another field, Avg.
**Table1**
MyID
ID1
ID2
ID3
**Table2**
MyID Data Mon
ID2 10 Jan
ID2 20 Feb
ID1 10 Jan
ID3 30 Mar
ID1 30 Mar
预期结果如下:
**Table1**
MyID Avg
ID1 20
ID2 15
ID3 30
也许在SQL中有更好的方法可以执行此操作,但目前尚不知道.
Maybe there's a better way to do this in SQL, but don't currently know.
推荐答案
UPDATE table1
INNER JOIN
(
SELECT Data1, AVG(columnName) avgCol
FROM table2
GROUP BY Data1
) b ON table1.MyID = b.Data
SET table1.avgColumnName = b.avgCol
- 具有连接的MS Access更新
- MS Access update with join
这篇关于使用表行的SQL foreach的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!