优先合并来自 n 字段的值 [英] Combine values from n-fields with precedence

问题描述

我有下面的示例表:

+----+----+----+|var1|var2|合并|+----+----+----+|a |b |a |+----+----+----+||c |c |+----+----+----+||x |x |+----+----+----+|c |d |c |+----+----+----+

我想合并 var1 和 var2 的值，规则是只要 var1 有一个值，就应该选择 var1代码>var2.

---

我下面的代码没有实现它，我不知道如何实现它.

SELECT id, var1 &var2 AS 合并从我的表

所有这些代码所做的都是连接 var1 和 var2 的数据，这不是我想要的.我之前问过的不再按预期工作.

+----+----+----+|var1|var2|合并|+----+----+----+|a |b |ab |+----+----+----+||c |c |+----+----+----+||x |x |+----+----+----+|c |d |cd |+----+----+----+

---

以下来自这个问题不采取考虑到孤独的价值:(

<预><代码>选择 ID、var1、var2、转变 (var1 不是 NULL, var1,var2 不是 NULL，var2，) AS 合并从我的表；

+----+----+----+|var1|var2|合并|+----+----+----+|a |b |a |+----+----+----+||c ||+----+----+----+||x ||+----+----+----+|c |d |c |+----+----+----+

---

更新

使用答案中提供的方法后，我看到一些我的记录实现了我在上面第一个表格中显示的内容.但在查看其他行后，我设法找到了被跳过的行，如第三个表所示.

下面是我使用的代码和结果截图.

SELECT ID, var1, var2,#方法一转变 (var1 不是 NULL, var1,var2 不是 NULL，var2) AS switchResult,# 方法二Nz(var1, var2) AS NzResult,# 方法三Iif(var1 IS NULL, var2, var1) AS IifResult从我的表；

这里的结果正是我想要的:

<块引用>

注意:ID、var1、var2、switchResult、NzResult、IifResult是指南的列名

但是当我搜索 (Blanks) 时，似乎有失败的行:

似乎 var1 的值如果 var2 的缺失返回正确的查询但是当 var2 确实有一个值并且 var1 没有，有些行没有返回正确的查询.

有谁知道为什么会这样?我已经检查过 var1 和 var2 是否包含空格，而它们没有.

解决方案

当 var1 不为 Null 时，你需要 var1，否则你需要 var2.您可以使用 Nz 来获取.

SELECT var1, var2, Nz(var1, var2) AS 合并从我的表；

如果您要从 Access 会话外部运行查询，则 Nz 函数将不可用.在这种情况下，您可以使用 IIf 表达式.

SELECT var1, var2, IIf(var1 Is Null, var2, var1) AS 合并从我的表；

如果 var1 可能包含零长度字符串 ("")，并且您希望将它们视为 Null --- 表示返回 var2 在那种情况下 --- 使用这个查询 ...

SELECT var1, var2, IIf(Len(var1 & "") = 0, var2, var1) AS 合并从我的表；

I have an example table below:

+----+----+------+
|var1|var2|merged|
+----+----+------+
|a   |b   |a     |
+----+----+------+
|    |c   |c     |
+----+----+------+
|    |x   |x     |
+----+----+------+
|c   |d   |c     |
+----+----+------+

I want to merge var1 and var2's values with the rule that whenever var1 has a value, it should be picked over var2.

---

My code below does not achieve it and I am at a loss on how to implement this.

SELECT id, var1 & var2 AS merged
FROM myTable

All this code does is concatenated var1 and var2's data which is not I want. The one I asked before is not working as desired anymore.

+----+----+------+
|var1|var2|merged|
+----+----+------+
|a   |b   |ab    |
+----+----+------+
|    |c   |c     |
+----+----+------+
|    |x   |x     |
+----+----+------+
|c   |d   |cd    |
+----+----+------+

---

The one below from this question does not take into account lone values :(

SELECT ID, var1, var2, 
  SWITCH (        
    var1 IS NOT NULL, var1,     
    var2 IS NOT NULL, var2,   
  ) AS merged
FROM myTable;

+----+----+------+
|var1|var2|merged|
+----+----+------+
|a   |b   |a     |
+----+----+------+
|    |c   |      |
+----+----+------+
|    |x   |      |
+----+----+------+
|c   |d   |c     |
+----+----+------+

---

UPDATE

After using the methods provided in the answers, I have seen some of my records achieve what I presented in the first table above. But after looking through the other rows I managed to find ones that were skipped, as presented in the third table.

Below is the code I use and screenshots of the results.

SELECT ID, var1, var2, 

      # method 1
      SWITCH (        
        var1 IS NOT NULL, var1,     
        var2 IS NOT NULL, var2
      ) AS switchResult,

      # method 2
      Nz(var1, var2) AS NzResult,

      # method 3
      Iif(var1 IS NULL, var2, var1) AS IifResult

FROM myTable;

The results here are what I wanted:

Note: ID, var1, var2, switchResult, NzResult, IifResult are the column names for guide

But as I search for (Blanks) there appears to be failed rows:

It seems that var1's values if var2's missing return correct queries but when var2 does have a value and var1 does not, some rows do not return correct queries.

Does anyone know why this happens? I already checked if var1 and var2 contains whitespace and they do not.

解决方案

You want var1 when it is not Null, and otherwise you want var2. You can use Nz to get that.

SELECT var1, var2, Nz(var1, var2) AS  merged
FROM myTable;

If you will be running the query from outside an Access session, the Nz function will not be available. In that case, you can use an IIf expression.

SELECT var1, var2, IIf(var1 Is Null, var2, var1) AS  merged
FROM myTable;

If var1 may contain zero-length strings (""), and you want those treated the same as Null --- meaning return var2 in that situation --- use this query ...

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

这篇关于优先合并来自 n 字段的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！