Question

请教数据库高手，在access2003里，用sql语句查询实现两表关联查询用count更新其中一表？

见图，两个表分别是T1,T2

假定T1A列的取值范围只可能是T2的A列里的数据。且T2的A列的值是不重复的没有多余行。
现在想实现的是把T1里A列与T2的A列对应的记录数汇总到C列。
拿图中数据举例，结果应该是
1 a 3
2 b 2
3 c 1
我自己sql不太熟悉，加上access里面似乎难度增加了不少，一直在报错必须有可更新的查询之类。
我把mdb文件上传了，大家可以下载来测试。
http://pan.baidu.com/s/1dDnJuZB





Answer 1

ACCESS数据库是可以用两个表关联的结果来更新其中一列表的。但是由于ACCESS Jet引擎功能较弱，它只能用数据表中实际存在的列的值来更新其中一张表，对于计算得来的列的列值是无法用于更新的，哪怕是事前将有查询关语句保存为一个固定名称的查询也不成。

例如 

update T2 a,(select A,count(A) as cnt from T1 group by A) b 
set a.C=b.cnt where b.A=a.A;

或者将其中的子查询“select A,count(A) as cnt from T1 group by A” 保存为查询“Q1”

上述语句改写如下

update T2 a,Q1 b set a.C=b.cnt where b.A=a.A;

上述两句的语法都没问题，但是无法执行（报错必须有可更新的查询）

这么办？因为cnt列是计算得来的，Jet无法利用该列更新T2的字段C，那我们就利用一张中间表T3来绕过这个限制。

先运行生成表查询，将上述子查询结果保存为表“T3”

select A,count(A) as cnt into T3 from T1 group by A;

再运行下列更新即可实现楼主的需求

update T2 a,T3 b set a.C=b.cnt where b.A=a.A;

如果以后要经常运行该操作，请先运行一下面两个动作查询来更新之前T3表里已有的数据，以免旧有数据影响更新查询的正确性。

1.清空旧数据

delete from T3;

2.存入新的子查询结果

insert into T3 SELECT A,count(a) as cnt from T1 group by A;

3.执行更新查询，更新T2表的C列

update T2 a,T3 b set a.C=b.cnt where b.A=a.A;

 

确实有些麻烦，但是目前暂时还未能找到不借助中间T3表的SQL语句解决方案。

*******

其实更新T2表的C列有些多余，因为我们可以非常方便地用选择查询得到你要的列表

select a.id,a.A,Nz(b.cnt,0) as C from T2 a left join 
(select A,count(A) as cnt from T1 group by a) b 
on b.A=a.A;

一句选择查询就搞定了，实在是没必要去花大把的精力来维护这个C列！

追问

谢谢，你回答得很认真，我测试下再说。

追答

再提供一个非连接的办法（使用DCount函数）。此法最方便，但是请注意这也是效率最低的方案！其理由我就不说了。
update T2 set C=Nz(DCount("[A]","T1","[A]='" & [A] & "'") ,0)

Answer 2

Select [T2].A,[T2].C from [T2] Left join Select * from (Select [T1].A, count(*) as 数量 from [T1] group by [T1].A) as [查询1] on [T2].A=[查询1].A;
如果你觉得写得累，可以用很简单的办法解决，直接把T1按A列统计一下，然后保存为查询1，再把查询1跟T2连接查询就可以了

追问

你上面的代码报“From”子句错误。

Answer 3

感觉上应该是用merge语句，可是我不会用access，不知道他里面有没有merge语句，而且merge没有insert我也不知道行不行，你试试吧。不行就算了。
merge into T2
using (select A,count(*) B from T1 group by A) TT
on T2.A=TT.A
WHEN MATCHED THEN
UPDATE SET
T2.C=TT.B;

追问

很遗憾没有，我也试过07版了。

Answer 4

select id,t2a,count(*) as c from (

select t1.a as t1a,t2.id,t2.a as t2a from t1,t2 where t1.a=t2.a
)
c就是你需要的数据

追问

你的语句复制出来语法都是错误，你上面是手打的根本没测试过。希望严谨一点好吗？

追答

怎么可能，你是完全复制的吧？你手敲一遍试试，我倒是觉得你不太适合做技术的。。。。

追问

我上面所有需要的资料都上传了，你如果测试过，肯定会把sql语句原封不动地复制到这里。如果你说你懒得下载、测试，我没什么好说的。

Answer 5

用程序分别链接sqlserver2008 和 ACCESS数据库
然后进行代码进行比较。

追问

没有程序，只是纯数据库里执行sql。还有，最好用sql语句来回答问题，不需要多余的建议。