不是牛人不要进:求改ACCESS的语句怎么表达?

[guiguigz]

有2个表，AnswerPaper答卷（总分）表、AnswerPaperData答题表
AnswerPaper
------------------
guidex Mark(试卷总分)
aaa``````17
bbb``````20

AnswerPaperData表：
QuestionGUID（题号）
Mark(该题得分)
-------
guidex AnswerPaper QuestionGUID Mark(该题得分)
1```````aaa `````````001``````````2 --》 5
2```````aaa``````````002``````````5
3```````aaa``````````003``````````10
4```````bbb``````````001``````````3 --》 5
5```````bbb``````````003``````````7
6```````bbb``````````004``````````10
我想把题号QuestionGUID=001 的得分都改成5分后更新答卷（总分）表，下面是重新计算答卷表总分分的更新语句：

UPDATE AnswerPaper As AP Set mark =(select sum(Mark) from AnswerPaperData as D where D.AnswerPaper = AnswerPaper.guidex)
where AP.guidex in(Select AnswerPaper from AnswerPaperData where
QuestionGUID = '001')

语句执行应该得到：
AnswerPaper
------------------
guidex Mark(试卷总分)
aaa```````20
bbb```````22

在SQLServer2000下是对的，请问怎么用jet-sql写出来并能在ACCESS下执行啊？

[OracleRoob]

--SQL Server的实现SQL如下：

--创建测试表
create table #AnswerPaper(guidex varchar(100), Mark int)

--追加测试数据
insert into #AnswerPaper select 'aaa',17
insert into #AnswerPaper select 'bbb',20

--创建测试表
create table #AnswerPaperData(guidex int, AnswerPaper varchar(100), QuestionGUID varchar(100), Mark int)


--追加测试数据
insert into #AnswerPaperData select 1,'aaa','001',5
insert into #AnswerPaperData select 2,'aaa','002',5
insert into #AnswerPaperData select 3,'aaa','003',10
insert into #AnswerPaperData select 4,'bbb','001',5
insert into #AnswerPaperData select 5,'bbb','003',7
insert into #AnswerPaperData select 6,'bbb','004',10

--更新前查看数据
select * from #AnswerPaper

--更新数据
UPDATE #AnswerPaper Set mark =(select sum(Mark) from #AnswerPaperData as D where D.AnswerPaper = AP.guidex)
from #AnswerPaper As AP
where AP.guidex in (Select AnswerPaper from #AnswerPaperData where QuestionGUID = '001')

--更新后查看数据
select * from #AnswerPaper

--销毁临时表
drop table #AnswerPaper,#AnswerPaperData

[OracleRoob]

Access使用的是Jet-SQL，而SQL Server使用的是T-SQL，两者用法上相差很大。

Access的Update语句中，对于含有聚合函数子查询的支持远比不上SQL Server，所以要使用域函数或借助临时表。



JET SQL 帮助(jet4 access2000)下载地址

http://www.access911.net/index.asp?board=8&recordid=75FAB71E&tt=

[OracleRoob]

--1、用域函数处理：dsum()

UPDATE AnswerPaper AS T SET Mark = dsum("Mark","AnswerPaperData","AnswerPaper='" & T.guidex & "'")


--2、把子查询的中间结果保存到临时表中，然后再用临时表和目标表关联更新。

[OracleRoob]

--Access使用以下两种方法处理：

--1、用域函数处理：dsum()

UPDATE AnswerPaper AS T SET Mark = dsum("Mark","AnswerPaperData","AnswerPaper='" & T.guidex & "'")


--2、把子查询的中间结果保存到临时表中，然后再用临时表和目标表关联更新。

--将中间数据生成临时表
SELECT AnswerPaperData.AnswerPaper, Sum(AnswerPaperData.Mark) AS Mark合计
into 新表
FROM AnswerPaperData
GROUP BY AnswerPaperData.AnswerPaper;

--用临时表关联要跟新的目标表更新
UPDATE AnswerPaper INNER JOIN 新表 ON AnswerPaper.guidex = 新表.AnswerPaper SET AnswerPaper.Mark = [新表].[Mark合计];


当然，还可以用VBA+SQL处理，但使用域函数是最简单的方法。

[OracleRoob]

--Access使用以下两种方法处理：

--1、用域函数处理：dsum()

UPDATE AnswerPaper AS T SET Mark = dsum("Mark","AnswerPaperData","AnswerPaper='" & T.guidex & "'")


--2、把子查询的中间结果保存到临时表中，然后再用临时表和目标表关联更新。

--将中间数据生成临时表
SELECT AnswerPaperData.AnswerPaper, Sum(AnswerPaperData.Mark) AS Mark合计
into 新表
FROM AnswerPaperData
GROUP BY AnswerPaperData.AnswerPaper;

--用临时表关联要跟新的目标表更新
UPDATE AnswerPaper INNER JOIN 新表 ON AnswerPaper.guidex = 新表.AnswerPaper SET AnswerPaper.Mark = [新表].[Mark合计];


当然，还可以用VBA+SQL处理，但使用域函数是最简单的方法。

[wwwwb]

JET SQL不是T-SQL，在ACCESS中用域函数：
UPDATE AnswerPaper AS T SET Mark = dsum("Mark","AnswerPaperData","AnswerPaper='" & T.guidex & "'")
在其它环境中先生成临时表，再替换
SELECT AnswerPaperData.AnswerPaper, Sum(AnswerPaperData.Mark) AS Mark合计
into TT
FROM AnswerPaperData
GROUP BY AnswerPaperData.AnswerPaper;

UPDATE AnswerPaper INNER JOIN TT ON AnswerPaper.guidex = TT.AnswerPaper SET AnswerPaper.Mark = [TT].[Mark合计];

[guiguigz]

^__^先谢谢各位大侠的帮助！！！！！！在下有礼了！！！！！

补充：
因为程序里是这样的：

String sql = "UPDATE AnswerPaper As AP Set mark =(select sum(Mark) from AnswerPaperData as D where D.AnswerPaper = AnswerPaper.guidex)
where AP.guidex in(Select AnswerPaper from AnswerPaperData where
QuestionGUID = '001')";
...
stmt = conn.createStatment();
stmt.executeUpdate(sql);
...

因为只是为了执行一条更新语句，我就想知道怎么能改写成一条ACCESS可以接受的语句？
因为SQLServer的兼容性比较好，现在要改在ACCESS下也能执行
非要分步操作才能办到吗？能不能通过一条语句就搞定？

[OracleRoob]

Access使用的是Jet-SQL，而SQL Server使用的是T-SQL，两者用法上相差很大。

Access的Update语句中，对于含有聚合函数子查询的支持远比不上SQL Server，所以要使用域函数或借助临时表。



你在外部通过ADO访问MDB，不能使用域函数，必须拆分成两个SQL语句。

[guiguigz]

--将中间数据生成临时表
（1）SELECT AnswerPaperData.AnswerPaper, Sum(AnswerPaperData.Mark) AS Mark合计
into 新表
FROM AnswerPaperData
GROUP BY AnswerPaperData.AnswerPaper;

--用临时表关联要跟新的目标表更新
（2）UPDATE AnswerPaper INNER JOIN 新表 ON AnswerPaper.guidex = 新表.AnswerPaper SET AnswerPaper.Mark = [新表].[Mark合计];
--------------------------
^__^再次感激各位的回帖！
以上（1）是把所有的试卷都求总分了，
大家 还遗漏了一个条件没有考虑：QuestionGUID = '001'
因为初衷是“有一题题目出题错误，所有使用到该题的试卷都相应给分，没有抽到这题的答卷就没改分”
还有正确的答案吗？谢谢各位的鼎立相助了！！！！

[OracleRoob]

如果你在外部访问MDB，只能拆分为两步:

--将中间数据生成临时表
SELECT AnswerPaperData.AnswerPaper, Sum(AnswerPaperData.Mark) AS Mark合计
into 新表
FROM AnswerPaperData
GROUP BY AnswerPaperData.AnswerPaper;

--用临时表关联要跟新的目标表更新
UPDATE AnswerPaper INNER JOIN 新表 ON AnswerPaper.guidex = 新表.AnswerPaper SET AnswerPaper.Mark = [新表].[Mark合计];

[wwwwb]

有一题题目出题错误，所有使用到该题的试卷都相应给分，没有抽到这题的答卷就没改分

具体一点，没有理解到是什么意思

[guiguigz]

说明：
从试题库中抽试题组成N份试卷，然后每个学生从这N份试卷中随机抽一份试卷答题，但是如果试卷上抽到同一题目的，因为这题可能出错了都给分，则相应抽到同一题（QuestionGUID = '001'）的试卷都给相应的分数。

^__^再次期待大家的回答！共同学习共同提高！谢谢~``

[OracleRoob]

在外部程序通过ADO访问MDB，没有比用临时表更简单的方法了。

如果你不嫌麻烦，可以写代码用数据集循环处理。

[wwwwb]

在JET SQL中上述SQL语句有错误，在ACCESS中用DSUM，其它环境中可以用生成临时表方法，
可以随机生成临时表名称

[OracleRoob]

Access的Jet-SQL的update语句中不支持子查询，你还是变通一下吧，这是限制，不是技术问题。

如果你在外部访问MDB，只能拆分为两步:

--将中间数据生成临时表
SELECT AnswerPaperData.AnswerPaper, Sum(AnswerPaperData.Mark) AS Mark合计
into 新表
FROM AnswerPaperData
GROUP BY AnswerPaperData.AnswerPaper;

--用临时表关联要跟新的目标表更新
UPDATE AnswerPaper INNER JOIN 新表 ON AnswerPaper.guidex = 新表.AnswerPaper SET AnswerPaper.Mark = [新表].[Mark合计];

[wwwwb]

楼主主要是要解决多人使用时生成临时表名称的问题，
可以用用户名+时间 OR 用唯一ID来作为临时表名称

[guiguigz]

因为通过生成新表来更新,会出现一个问题,因为每次"into 新表"不是把新表数据都删除了添加新的数据吗?这样如果有多个人同时操作更新,都into到同一个表是行不通的吧?
我想能不能通过查询出结果集,把结果集As出一个别名后进行更新?
方法(1)
UPDATE AnswerPaper as A,
`(SELECT sum(Mark) as ccc,AnswerPaper FROM AnswerPaperData where AnswerPaper in
`` (SELECT AnswerPaper FROM AnswerPaperData where QuestionGUID='001') group by AnswerPaper) as B
Set A.Mark = B.ccc
Where A.guidex = B.AnswerPaper
以上提示"操作必须使用一个可更新的查询"
----------------------------
方法(2),使用INNER JOIN
UPDATE AnswerPaper as A
INNER JOIN
`(SELECT sum(Mark) as ccc,AnswerPaper FROM AnswerPaperData where AnswerPaper in
```(SELECT AnswerPaper FROM AnswerPaperData where QuestionGUID='001') group by AnswerPaper)as B
ON A.guidex=B.AnswerPaper
Set A.Mark = B.ccc
以上提示"操作必须使用一个可更新的查询"

这样的修改可行吗?

[guiguigz]

^___^
谢谢大家的帮助！问题解决了~~！
周一我整理我的答案后发上来给大家，也让后续学习的人有个明确的结果

PS：问一下，怎么给各位加分？

[OracleRoob]

右上角-->管理-->输入密码-->给分-->结贴

[guiguigz]

^__^再次谢谢各位的帮助!以下是代码,终于可以结帖散分了!
答案:
String sql="..."; //改分的SQL
long temp = new Date().getTime(); //生成临时表文件名
String sql2 = "SELECT Sum(Mark) as ccc,AnswerPaper "
```````````````+"INTO " + temp
```````````````+" FROM AnswerPaperData where AnswerPaper in (SELECT AnswerPaper From AnswerPaperData where QuestionGUID = '"
```````````````+QuestionGUID
```````````````+"') group by AnswerPaper";
String sql3 ="UPDATE AnswerPaper as A," + temp +" As B set A.Mark = B.ccc where A.GUIDEX=B.AnswerPaper";
String sql4 = "DROP TABLE " + temp ;
``try{
``` Connection conn = ...;
`` conn.setAutoCommit(false); //关闭自动提交,开启事务
``` Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUT_UPDATABLE);
```````stmt.executeUpdate(sql); //给试卷加分
```````stmt.executeUpdate(sql2); //将SELECT的结果插入临时表
```````stmt.executeUpdate(sql3); //更新,执行UPDATE语句
```````stmt.executeUpdate(sql4); //删除临时表temp
```}catch(Execption e){
`````e.printStrackTrace();
``` try{
``````````conn.rollback(); //发生例外则回滚
`````````}catch(Execption e1){ e1.printStackTrace() }
```}
finally{
````DbUtils.closeQuietly(conn,stmt,rs);
}