比较两行并识别值不同的列 [英] Compare two rows and identify columns whose values are different
问题描述
情况
我们有一个应用程序，我们将机器设置存储在 SQL 表中.当用户更改机器的参数时，我们会创建一个修订版"，这意味着我们将一行插入到表中.该表有大约 200 个列.在我们的应用程序中，用户可以查看每个修订版.
We have an application where we store machine settings in a SQL table. When the user changes a parameter of the machine, we create a "revision", that means we insert a row into a table. This table has about 200 columns. In our application, the user can take a look on each revision.
问题
我们要突出显示自上次修订以来已更改的参数.
We want to highlight the parameters that have changed since the last revision.
问题
是否有一种仅使用 SQL 的方法来获取两行之间差异的列名?
Is there an SQL-only way to get the column names of the differences between two rows?
示例
ID | p_x | p_y | p_z
--------------------
11 | xxx | yyy | zzz
12 | xxy | yyy | zzy
查询应返回 p_x 和 p_z.
编辑
该表有 200 列，而不是行...
The table has 200 columns, not rows...
我的出路
我的目的是为这个问题找到一个单行 SQL 语句".
My intention was to find a "one-line-SQL-statement" for this problem.
我在下面的答案中看到，它在 SQL 中更重要.由于此问题没有包含 SQL 的简短解决方案，因此在我们的软件 (c#) 后端解决它当然要容易得多！
I see in the answers below, it's kind a bigger thing in SQL. As there is no short, SQL-included solution for this problem, solving it in the backend of our software (c#) is of course much easier!
但由于这不是我的问题的真正答案"，我没有将其标记为已回答.
But as this is not a real "answer" to my question, I don't mark it as answered.
感谢您的帮助.
推荐答案
你说:
We want to highlight the parameters that have changed since the last revision.
这意味着您希望显示(或报告)能够突出显示更改的参数.
This implies that you want the display (or report) to make the parameters that changed stand out.
如果您无论如何要显示所有参数，在前端以编程方式执行此操作会容易得多.在编程语言中，这将是一个简单得多的问题.不幸的是，不知道你的前端是什么，我不能给你特别的建议.
If you're going to show all the parameters anyway, it would be a lot easier to do this programmatically in the front end. It would be a much simpler problem in a programming language. Unfortunately, not knowing what your front end is, I can't give you particular recommendations.
如果你真的不能在前端做，但必须从数据库的查询中接收这些信息(你确实说仅SQL")，你需要指定你想要的数据格式in. 在两条记录之间更改的列的单列列表?带有标志的列列表，指示哪些列发生了变化或未发生变化?
If you really can't do it in the front end but have to receive this information in a query from the database (you did say "SQL-only"), you need to specify the format you'd like the data in. A single-column list of the columns that changed between the two records? A list of columns with a flag indicating which columns did or didn't change?
但这是一种可行的方法，尽管在此过程中它会在进行比较之前将所有字段转换为 nvarchars:
But here's one way that would work, though in the process it converts all your fields to nvarchars before it does its comparison:
- 使用此处描述的技术 a>(免责声明:这是我的博客)将您的记录转换为 ID-名称-值对.
在 ID 上将结果数据集与自身连接起来，以便您可以比较这些值并打印那些已更改的值:
- Use the technique described here (disclaimer: that's my blog) to transform your records into ID-name-value pairs.
Join the resulting data set to itself on ID, so that you can compare the values and print those that have changed:
with A as (
-- We're going to return the product ID, plus an XML version of the
-- entire record.
select ID
, (
Select *
from myTable
where ID = pp.ID
for xml auto, type) as X
from myTable pp )
, B as (
-- We're going to run an Xml query against the XML field, and transform it
-- into a series of name-value pairs. But X2 will still be a single XML
-- field, associated with this ID.
select Id
, X.query(
'for $f in myTable/@*
return
<data name="{ local-name($f) }" value="{ data($f) }" />
')
as X2 from A
)
, C as (
-- We're going to run the Nodes function against the X2 field, splitting
-- our list of "data" elements into individual nodes. We will then use
-- the Value function to extract the name and value.
select B.ID as ID
, norm.data.value('@name', 'nvarchar(max)') as Name
, norm.data.value('@value', 'nvarchar(max)') as Value
from B cross apply B.X2.nodes('/myTable') as norm(data))
-- Select our results.
select *
from ( select * from C where ID = 123) C1
full outer join ( select * from C where ID = 345) C2
on C1.Name = c2.Name
where c1.Value <> c2.Value
or not (c1.Value is null and c2.Value is null)
这篇关于比较两行并识别值不同的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
