动态SQL列值重复和差异检测合并查询 [英] Dynamic SQL column value duplicate and difference detection merge query

问题描述

我有 n 个列，其中可能包含唯一或重复值，这意味着每一列可能具有不同的值或相同的值.

我要实现的是一个可以分析 n 列的MS Access脚本，该脚本可以执行以下操作:

• 如果只有一个的列具有值，其余列为空白，则最终值将是唯一看到的值(注意:该值已完成).

  +-----+----+----+-----+
  |Var1 |Var2|Var3|Final|
  +-----+----+----+-----+
  |1    |    |    |1    |
  +-----+----+----+-----+
  |     |    |3   |3    |
  +-----+----+----+-----+
  |     |yes |    |yes  |
  +-----+----+----+-----+
  

• 如果存在多个不同的值，则要求用户在这些值之间进行选择(更新:变量的优先级，而不是用户选择的值，即Var1> Var2> ...> VarN).

  +----+----+----+-----+
  |Var1|Var2|Var3|Final|
  +----+----+----+-----+
  |1   |3   |    |?    |
  +----+----+----+-----+
  |ok  |    |3   |?    |
  +----+----+----+-----+
  |    |yes |5   |?    |
  +----+----+----+-----+
  

• 如果有多个相同的值，请继续使用该值作为最终值.

  +----+----+----+-----+
  |Var1|Var2|Var3|Final|
  +----+----+----+-----+
  |1   |1   |    |1    |
  +----+----+----+-----+
  |ok  |    |ok  |ok   |
  +----+----+----+-----+
  |    |yes |yes |yes  |
  +----+----+----+-----+
  

解决方案

1. 解决所有三种情况，下面的代码负责内容中的优先级，空值和可能为null的值. INTO temp会将查询结果保存到名为temp的表中.

   SELECT id, var1, var2, 
       IIf(Len(var1 & "") = 0, var2, var1) AS merged
   INTO temp
   FROM myTable;
   

2. 将记录转移到源表中.

   UPDATE myTable 
       INNER JOIN temp 
         ON [myTable].ID=temp.ID 
   SET myTable.merged= [temp].[merged];
   

问题" 从n个字段中优先合并值 "！

>

I have n-columns that may contain unique or duplicated values meaning each column may have different values or the same ones.

What I want to achieve is an MS Access script that can analyze n-columns that could do the following:

• If the columns if only one has a value and the rest are blank then the final value will be the only one seen (Note: this one is already accomplished).

  +-----+----+----+-----+
  |Var1 |Var2|Var3|Final|
  +-----+----+----+-----+
  |1    |    |    |1    |
  +-----+----+----+-----+
  |     |    |3   |3    |
  +-----+----+----+-----+
  |     |yes |    |yes  |
  +-----+----+----+-----+
  

• If there are more than one different values then ask the user to choose between these values (Update: precedence of variables instead of user selection ie. Var1 > Var2 > ... > VarN).

  +----+----+----+-----+
  |Var1|Var2|Var3|Final|
  +----+----+----+-----+
  |1   |3   |    |?    |
  +----+----+----+-----+
  |ok  |    |3   |?    |
  +----+----+----+-----+
  |    |yes |5   |?    |
  +----+----+----+-----+
  

• If there are more than one same values then proceed to use that as the final value.

  +----+----+----+-----+
  |Var1|Var2|Var3|Final|
  +----+----+----+-----+
  |1   |1   |    |1    |
  +----+----+----+-----+
  |ok  |    |ok  |ok   |
  +----+----+----+-----+
  |    |yes |yes |yes  |
  +----+----+----+-----+
  

解决方案

1. Solve all three situations, the code below takes care of precedence, empty and possible null values in the content. INTO temp will save the query result into a table named temp.

   SELECT id, var1, var2, 
       IIf(Len(var1 & "") = 0, var2, var1) AS merged
   INTO temp
   FROM myTable;
   

2. Transfer the records into the source table.

   UPDATE myTable 
       INNER JOIN temp 
         ON [myTable].ID=temp.ID 
   SET myTable.merged= [temp].[merged];
   

The question "Combine values from n-fields with precedence" helped!

这篇关于动态SQL列值重复和差异检测合并查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！