本文介绍了使用表行的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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

11-02 23:22