如何从两个表中查找不相等的值 [英] How to find Unequal Values from Two Tables

问题描述

我正在做一个左联接查询，仅在F1列相同的情况下，比较两个表中是否在f9和sumoff6列上彼此不相等的任何值.如果它们不同，我想减去它们.我得到的结果显示两个表上已经相等的值.我需要f1列在两个表上都匹配，但是如果它们在sumoff6和f9列上的值不匹配，则显示它们并减去它们.我正在使用MS Access SQL视图.

I am doing a left join query to compare the two tables for any values that do not equal each other on f9 and sumoff6 columns ONLY if the F1 columns are the same. If they are different i would like to subtract them. The results i am getting shows equal values that are already on both tables. I need for f1 columns to match on both tables but if their values on the sumoff6 and f9 columns do not match then display them and subtract them. I am using MS Access SQL view.

查询

SELECT statement.f1, statement.f9
FROM statement 
LEFT JOIN allocation_final ON statement.[f1] = allocation_final[f1]
WHERE [allocation_final].sumoff6 <> statement.f9

声明表:

f1     f9
-----------------
1      135.58
2      166.30
3       40.22 
4       86.46
5       170.33
6       96.40

allocation_final:

allocation_final:

f1     SumOff6
--------------
1      135.58
2      166.30
3       40.00
4       86.46
5       170.33
6       40.22 
7       22.40
8       70.00
9       96.40
10      50.00

结果

f1          f9                 
------------------
1   135.58
2   166.3
4    86.46
5   170.33

推荐答案

问题是allocation_final.sumoff6具有NULL值，并且WHERE条件过滤出了NULL值.只需测试这种情况:

The problem is that allocation_final.sumoff6 has a NULL value, and the WHERE condition filters out NULL values. Just test for this condition:

SELECT statement.f1, statement.f9
FROM statement LEFT JOIN
     allocation_final
     ON statement.[f1] = allocation_final[f1] 
WHERE [allocation_final].sumoff6 <> statement.f9 OR
      [allocation_final].sumoff6 IS NULL;

也许您的加入方向错误:

Perhaps your join is in the wrong direction:

SELECT allocation_final.f1, statement.f9, allocation_final.sumoff6
FROM allocation_final LEFT JOIN
     statement
     ON statement.[f1] = allocation_final[f1] 
WHERE allocation_final.sumoff6 <> statement.f9 OR
      statement.f9 IS NULL;

根据您的样本数据，这会更有意义.

Based on your sample data, this would make more sense.

这篇关于如何从两个表中查找不相等的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！