多表对比核对的SQL查询语句如何写？

lijinyang

以下附件中有三个表。其中RA2008表中如果标识为1的数据与SHEET1表的数据比对，标识为2的数据与SHEET2比对，比对的条件是RA2008表中的主机卡号与SHHET1或SHEET2的卡号相比较，RA2008表中“交易日期”“交易金额”分别与SHEET1或SHEET2表中的“交易时间”“交易金额”相比较。将不相同的记录生成一个新表。

lijinyang

补上附件。

Scarlett_88

RA2008表在哪？

gteng

继续关注中。。。。

lijinyang

不好意思，那天将文件传错了，现补上，请高手出手相助。。。。

lijinyang

要注意的是，交易日期的格式RA2008表与SHEET1、SHEET2表中的时间不同。如有可能的话，将交易金额也对比一下。

lijinyang

高手呢？非常期待你的帮忙！！！

mengshu

呵呵，我也在找对比数据的方法

mengshu

高手帮忙解决啊

zez

不太明白楼主要达到的目的,所以这里所做的两个查询仅供参考,楼主可在此基础上按自己的要求加以修改:
1.
SELECT ra2008.主机卡号, A.交易金额, CDbl(nz([ra2008.交易金额]))/100 AS ra2008表金额, Left(A.交易时间,4) & Mid(A.交易时间,6,2) & Mid(A.交易时间,9,2) AS SHEET表日期, ra2008.交易日期, "交易日期" & IIf(Left(A.交易时间,4) & Mid(A.交易时间,6,2) & Mid(A.交易时间,9,2)=ra2008.交易日期,"","不") & "相等" AS 表达式1, "交易金额" & IIf(A.交易金额=CDbl(nz([ra2008.交易金额])/100),"","不") & "相等" AS 表达式2, ra2008.互代标志
FROM ra2008 LEFT JOIN (SELECT CStr(nz(sheet2.卡号)) AS 卡号_, sheet2.交易金额, Left(sheet2.交易时间,4) & Mid(sheet2.交易时间,6,2) & Mid(sheet2.交易时间,9,2) AS SHEET表日期, sheet2.ID, sheet2.银行代码, sheet2.主帐号, sheet2.交易代码, sheet2.终端编号, sheet2.人行跟踪号, sheet2.交易时间, sheet2.完成情况
FROM sheet2) as
A ON ra2008.主机卡号 = A.卡号_
WHERE (((ra2008.互代标志)="2"));
2
SELECT ra2008.主机卡号, sheet1.交易金额, CDbl([ra2008.交易金额])/100 AS ra2008表金额, Left(sheet1.交易时间,4) & Mid(sheet1.交易时间,6,2) & Mid(sheet1.交易时间,9,2) AS SHEET表日期, ra2008.交易日期, "交易日期" & IIf(Left(sheet1.交易时间,4) & Mid(sheet1.交易时间,6,2) & Mid(sheet1.交易时间,9,2)=ra2008.交易日期,"","不") & "相等" AS 表达式1, "交易金额" & IIf(sheet1.交易金额=CDbl([ra2008.交易金额])/100,"","不") & "相等" AS 表达式2, ra2008.互代标志
FROM ra2008 LEFT JOIN sheet1 ON ra2008.主机卡号 = sheet1.卡号
WHERE (((ra2008.互代标志)="1"));