# Access 数据操作

access数据库的操作与标准数据库SQL有差距

# 查询汇总数据再更新

在更新时如果是连表更新，要分开

先将汇总的结果存到临时表
再通过临时表更新表数据

SELECT LSZD,Sum(JZMJ) , Sum(ZDMJ) FROM FW_K_320305 group by LSZD;

UPDATE ZD_K_320305 AS d INNER JOIN ZD_K_320305_temp AS c ON d.DJH = c.DJH SET d.ZDZMJ = c.ZDMJ, d.JZZMJ = c.JZMJ;

1
2
3

SELECT * FROM FW_K_320305 where LSZD='320305104123JC00004';

UPDATE ZD_K_320305 AS d INNER JOIN (SELECT a.DJH, sum(b.JZMJ) as JZMJ, sum(b.ZDMJ) as ZDMJ
                     from ZD_K_320305 as a
                              left join FW_K_320305 as b on a.DJH = b.LSZD
                     group by a.DJH)  AS c ON d.DJH = c.DJH SET d.ZDZMJ = c.ZDMJ, d.JZZMJ = c.ZDMJ;

UPDATE  (SELECT a.DJH, sum(b.JZMJ) as JZMJ, sum(b.ZDMJ) as ZDMJ
                     from ZD_K_320305 as a
                              left join FW_K_320305 as b on a.DJH = b.LSZD
                     group by a.DJH)  AS c INNER JOIN  ZD_K_320305 AS d ON d.DJH = c.DJH SET d.ZDZMJ = c.ZDMJ, d.JZZMJ = c.ZDMJ;

1
2
3
4
5
6
7
8
9
10
11

Excel操作 →