如何比较两行或获取值与所比较的行不匹配的字段？ [英] How to compare two rows or get the fields which value is not match with the rows that compared?

问题描述

我试图获取的值不匹配的字段的比较行。

I'm trying to get the fields which value is not match base on the compared rows. It's hard to explain so I'll put the sample table and its results.

表：订单

|  seqid  |  orderId  |  taskId  |  field1  |  field2  |  field3  |  field4  |
+---------+-----------+----------+----------+----------+----------+----------+ 
|  1      |  1        |  1       |  a       |  b       |  c       |  d       |
|  2      |  1        |  2       |  a       |  b       |  c       |  d       |
|  3      |  2        |  1       |  a       |  b       |  c       |  d       |
|  4      |  2        |  2       |  a       |  c       |  c       |  c       |
|  5      |  3        |  1       |  a       |  a       |  a       |  a       |

结果：

|  OrderId  |  FieldName  |  Error  |
+-----------+-------------+---------+
|  2        |  field2     |  1      |
|  2        |  field4     |  1      |

在此可以看到 orderId 使用 taskId 进行比较。从field1到field4是要检查其值是否不匹配的字段。如果是这样，那么获取字段名称。列错误不是优先级，您可以忽略它。如果orderId只有一个任务，那么它不会被比较。

Here you can see that the orderId will be compared using its taskId. From field1 to field4 are the fields to be check if their values are not match. If so, then get the field name. The column Error is not a priority, you can disregard it. If the orderId has only one task then it will not be compared.

我的第一个计划是使用两个查询，将分隔 taskId 然后Java将做剩下的。但是如果可能的话，只要一个查询就会很好。即使我不知道存储过程，如果它是唯一的方式，那么让我展示，我会检查它。

My first plan is to use two query that will separate the taskId then Java will do the rest. But if possible with just a query that would be great. Even though I'm not knowledgeable with stored procedure, if its the only way then let me show and I'll check on it.

我没有任何工作查询还是甚至得到接近我想要的结果。任何答案将不胜感激。非常感谢！

I don't have any working query yet or even get a result as close to what I wanted. Any answer will be appreciate. Thanks!

UPDATED：

我想结果很难回答。我会发布另一个我认为很容易解决的结果。

I guess the results is difficult to answer. I'll post another result which is I think much easy to solve.

Results2

|  orderId  |  field1  | field2  | field3  |  field4  |
+-----------+----------+---------+---------+----------+
|  1        |  0       | 0       | 0       |  0       |
|  2        |  0       | 1       | 0       |  1       |

对于第二个结果，我需要知道如果字段有不匹配的值，如果是，它将显示1。这个结果的过程是比较两行，即 Task = 1 和 Task = 2 code> orderId 。我将过滤在Java中具有无法比拟的值的字段。

For the second results I need to know if the field has unmatched value if yes it will display 1. The process of this results is to compare the two rows which is the Task = 1 and Task = 2 with same orderId. I will filter the fields which have unmatched value in Java.

我希望有人在这里可以回答它，即使是第二个结果。如果你能回答第一个结果，那将是一个很大的帮助。

I hope someone here can answer it even the second results. If you can answer the first results, that would be a great help.

推荐答案

SELECT
  orderId,
  CASE MIN(field1) WHEN MAX(field1) THEN 0 ELSE 1 END AS field1,
  CASE MIN(field2) WHEN MAX(field2) THEN 0 ELSE 1 END AS field2,
  ...
FROM atable
GROUP BY
  orderId

这篇关于如何比较两行或获取值与所比较的行不匹配的字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！