比较表中的行以了解字段之间的差异 [英] Comparing rows in table for differences between fields

问题描述

我有一个包含20多个列的表(客户端)，大部分都是历史数据.

I have a table (client) with 20+ columns that is mostly historical data.

类似: id | clientID | field1 | field2 | etc ... | updateDate

Something like: id|clientID|field1|field2|etc...|updateDate

如果我的数据如下:

    
    10|12|A|A|...|2009-03-01
    11|12|A|B|...|2009-04-01
    19|12|C|B|...|2009-05-01
    21|14|X|Y|...|2009-06-11
    27|14|X|Z|...|2009-07-01

是否有一种简单的方法可以比较每一行并突出显示字段中的差异? 我需要能够简单地突出显示各修订版本之间更改的字段(密钥和课程日期除外)

Is there an easy way to compare each row and highlight the differences in the fields? I need to be able to simply highlight the fields that changed between revisions (except for the key and the date of course)

每个新行中可能会更新多个字段(或只有一个).

There may be multiple fields updated in each new row (or just one).

这将在每个客户端的基础上进行，因此我可以选择clientID进行过滤.

This would be on a client by client basis so I could select on the clientID to filter.

它可能在服务器或客户端，这是最简单的.

It could be on the server or client side, which ever is easiest.

更多详细信息 我应该稍微扩展一下我的描述: 我只是想看看字段之间是否存在差异(一个字段在任何方面都不同).一些数据是数字，一些是文本，其他是日期.一个更完整的示例可能是:

More details I should expand my description a little: I'm looking to just see if there was a difference between the fields (one is different in any way). Some of the data is numeric, some is text others are dates. A more complete example might be:

    
    10|12|A|A|F|G|H|I|J|...|2009-03-01
    11|12|A|B|F|G|H|I|J|...|2009-04-01
    19|12|C|B|F|G|Z|I|J|...|2009-05-01 ***
    21|14|X|Y|L|M|N|O|P|...|2009-06-11
    27|14|X|Z|L|M|N|O|P|...|2009-07-01

我希望能够播放clientID 12的每一行，并高亮显示11行中的B和C&第19行中的Z.

I'd want to be able to isplay each row for clientID 12 and highlight B from row 11 and C & Z from row 19.

推荐答案

SQL中的任何表达式都只能引用一行中的列(禁止子查询).

Any expression in SQL must reference columns only in one row (barring subqueries).

A JOIN可用于将两个不同的行变成结果集的一行.

A JOIN can be used to make two different rows into one row of the result set.

因此，您可以通过自联接比较不同行上的值.这是一个示例，该示例显示将每一行连接到与同一客户端关联的每隔一行(不包括将一行连接到自身):

So you can compare values on different rows by doing a self-join. Here's an example that shows joining each row to every other row associated with the same client (excluding a join of a row to itself):

SELECT c1.*, c2.*
FROM client c1
JOIN client c2 ON (c1.clientID = c2.clientID AND c1.id <> c2.id)

现在，您可以编写比较列的表达式.例如，将上述查询限制为field1不同的查询:

Now you can write expressions that compare columns. For example, to restrict the above query to those where field1 differs:

SELECT c1.*, c2.*
FROM client c1
JOIN client c2 ON (c1.clientID = c2.clientID AND c1.id <> c2.id)
WHERE c1.field1 <> c2.field1;

您无需指定需要进行哪种比较，因此我将其留给您.关键点在于，通常，您可以使用自联接来比较给定表中的行.

You don't specify what kinds of comparisons you need to make, so I'll leave that to you. The key point is that in general, you can use a self-join to compare rows in a given table.

对您的评论和说明进行澄清:好的，因此，您的差异"不仅仅是根据值，而是根据行的顺序位置.请记住，关系数据库没有行号的概念，相对于您必须在ORDER BY子句中指定的某些顺序而言，它们仅具有行顺序.不要将"id"伪键与行号混淆，仅在实现它们的同时，数字被分配为单调递增.

Re your comments and clarification: Okay, so your "difference" is not simply by value but by ordinal position of the row. Remember that relational databases don't have a concept of row number, they only have order of rows with respect to some order you must specify in an ORDER BY clause. Don't confuse the "id" pseudokey with row number, the numbers are assigned as monotonically increasing only by coincidence of their implementation.

在MySQL中，您可以利用用户定义的优势变量以达到您想要的效果.按clientId然后按id排序查询，并跟踪MySQL用户变量中每列的值.当当前行中的值与变量中的值不同时，请执行将要突出显示的所有操作.我将显示一个字段的示例:

In MySQL, you could take advantage of user-defined variables to achieve the effect you're looking for. Order the query by clientId and then by id, and track values per column in MySQL user variables. When the value in a current row differs from the value in the variable, do whatever highlighting you were going to do. I'll show an example for one field:

SET @clientid = -1, @field1 = '';
SELECT id, clientId, field1, @clientid, @field1,
  IF(@clientid <> clientid, 
    ((@clientid := clientid) AND (@field1 := field1)) = NULL,
    IF (@field1 <> field1, 
      (@field1 := field1), 
      NULL
    )
  ) AS field1_changed
FROM client c
ORDER BY clientId, id;

请注意，此解决方案与仅使用普通SQL选择所有行并在获取行时使用应用程序变量跟踪值并没有什么不同.

Note this solution is not really different from just selecting all rows with plain SQL, and tracking the values with application variables as you fetch rows.

这篇关于比较表中的行以了解字段之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！