使用表行的SQL foreach [英] SQL foreach using table rows

问题描述

我有两个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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！