我在StackOverflow上浏览了各种主题,但是找不到适合我问题的正确解决方案。这是我所拥有的:
我有两个数据库表。一个表应该是某种参考模型,看起来类似于此示例:
+----+----------------+----------------+----------------+-----------------+
| ID | FIELD1 | FIELD2 | FIELD3 | FIELD 4 |
+----+----------------+----------------+----------------+-----------------+
| 1 | Value1_Field_1 | Value1_Field_2 | Value1_Field_3 | Value1_Field_4 |
| 2 | Value2_Field_1 | Value2_Field_2 | Value2_Field_3 | Value2_Field_4 |
| 3 | Value3_Field_1 | Value3_Field_2 | Value3_Field_3 | Value3_Field_4 |
| 4 | Value4_Field_1 | Value4_Field_2 | Value4_Field_3 | Value4_Field_4 |
| 5 | Value5_Field_1 | Value5_Field_2 | Value5_Field_3 | Value5_Field_4 |
+----+----------------+----------------+----------------+-----------------+
现在,我将新数据输入第二个表。这些数据可以具有相同的值,也可以具有相同的行数。但是也可能发生,某些数据有更多行,或者行内的值不同。这是另一个表格示例,其中我多一行并且两个值不同:
+----+----------------+-----------------+-----------------+----------------+
| ID | FIELD1 | FIELD2 | FIELD3 | FIELD 4 |
+----+----------------+-----------------+-----------------+----------------+
| 1 | Value1_Field_1 | Value1_Field_2 | Value1_Field_3 | Value1_Field_4 |
| 2 | Value2_Field_1 | Value2_Field_2 | Value2_Field_3 | Value2_Field_4 |
| 3 | Value3_Field_1 | Value3_Field_2 | Value3_NEWVALUE | Value3_Field_4 |
| 4 | Value4_Field_1 | Value4_Field_2 | Value4_Field_3 | Value4_Field_4 |
| 5 | Value5_Field_1 | Value5_NEWVALUE | Value5_Field_3 | Value5_Field_4 |
| 6 | Value6_Field_1 | Value6_Field_2 | Value6_Field_3 | Value6_Field_4 |
+----+----------------+-----------------+-----------------+----------------+
我正在寻找比较这两个表并列出所有不同记录的SQL语句。在上面的示例中,SQL语句应重新浏览这些信息:
+----+----------------+-----------------+-----------------+----------------+
| ID | FIELD1 | FIELD2 | FIELD3 | FIELD 4 |
+----+----------------+-----------------+-----------------+----------------+
| 3 | | | Value3_NEWVALUE | |
| 5 | | Value5_NEWVALUE | | |
| 6 | Value6_Field_1 | Value6_Field_2 | Value6_Field_3 | Value6_Field_4 |
+----+----------------+-----------------+-----------------+----------------+
到目前为止,这是我尝试过的:
SELECT distinct FIELD1, FIELD2, FIELD3, FIELD4 from table_references
union
SELECT distinct FIELD1, FIELD2, FIELD3, FIELD4 from table_new_data
该语句返回所有行,但仅返回一次。这不是我想要的。我也用以下代码尝试过:
SELECT FIELD1, FIELD2, FIELD3, FIELD4
FROM (
SELECT FIELD1, FIELD2, FIELD3, FIELD4 FROM table_references
UNION ALL
SELECT FIELD1, FIELD2, FIELD3, FIELD4 FROM table_new_data
) tbl
GROUP BY FIELD1
HAVING count(*) = 1
ORDER BY FIELD1
该代码仅返回第6行,但不会向我显示ID 3和5中的新值。
任何帮助将非常感激。提前致谢!
编辑:根据@ Madhur Bhaiya解决方案,我犯了一个错误。输出应如下所示:
+----+----------------+-----------------+-----------------+----------------+
| ID | FIELD1 | FIELD2 | FIELD3 | FIELD 4 |
+----+----------------+-----------------+-----------------+----------------+
| 3 | Value3_Field1 | Value3_Field2 | Value3_NEWVALUE | Value3_Field4 |
| 5 | Value5_Field1 | Value5_NEWVALUE | Value5_Field3 | Value5_Field4 |
| 6 | Value6_Field_1 | Value6_Field_2 | Value6_Field_3 | Value6_Field_4 |
+----+----------------+-----------------+-----------------+----------------+
因此,我还需要受影响的行中所有值都不同的值。
我们可以LEFT JOIN
用来比较两个表,然后使用条件函数,例如相应If()
地获取值:
询问
SELECT tnew.*
FROM table_new_data AS tnew
LEFT JOIN table_references AS told
ON told.ID = tnew.ID
WHERE (told.ID IS NOT NULL AND
(tnew.FIELD1 <> told.FIELD1 OR
tnew.FIELD2 <> told.FIELD2 OR
tnew.FIELD3 <> told.FIELD3 OR
tnew.FIELD4 <> told.FIELD4)
) OR
told.ID IS NULL;
结果
| ID | FIELD1 | FIELD2 | FIELD3 | FIELD4 |
| --- | -------------- | --------------- | --------------- | -------------- |
| 3 | Value3_Field_1 | Value3_Field_2 | Value3_NEWVALUE | Value3_Field_4 |
| 5 | Value5_Field_1 | Value5_NEWVALUE | Value5_Field_3 | Value5_Field_4 |
| 6 | Value6_Field_1 | Value6_Field_2 | Value6_Field_3 | Value6_Field_4 |
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句