合并行代码返回空内容 [英] Merge rows code returns empty content

问题描述

我正在合并同一个表中的两列.我正在关注此线程中的答案.

I am merging two columns in the same table. I am following the answer in this thread.

我有一个看起来像这样的表，var 作为合并变量(它有一个 id 但不包括在这里):

I have a table that looks like this and the var as the merged variable (it has an id but not included here):

var1    var2    var
1               1
        2       2
8               8    
        10      10        

我使用下面的代码来获取相关列的所有值，这些值完美地工作.两个相关的列与它们各自的值合二为一.

I use the code below to get all values for the concerned columns which works perfectly. The two concerned columns are combined into one with their respective values.

# code1
SELECT ID, "var1" AS var, var1 AS val FROM source
UNION ALL
SELECT ID, "var2" AS var, var2 AS val FROM source

接下来是我需要获取每行和每列组合的唯一值:

Next is I need to get the unique values per row and column combination:

# code2
SELECT ID, First(val) AS FirstOfVal
FROM (
    SELECT DISTINCT ID, val
    FROM code1
    WHERE val IS NOT NULL
  )
GROUP BY ID
HAVING COUNT(*) = 1

在 code2 中，我希望得到一个表，其中 FirstOfVal 列填充了值，但在我的情况下没有内容.

In code2 I expect to get a table with a FirstOfVal column filled with values but no contents in my case.

我该如何解决这个问题?

How do I solve this?

我尝试在其他列组合上使用它，并且对于我正在修改的这个列，它完美地除了.我能想到的这些列我在合并时遇到问题的唯一区别是它们包含很多空行(如上所示)并且每列都有自己的值，没有两行的列同时具有两个值.

I tried using it on other combinations of columns and it worked perfectly except for this column that I'm modifying. The only difference I can think of these columns I'm having a problem merging is that they contain a lot of empty rows (as seen above) and each column has its own value, no two rows' columns have both values.

推荐答案

好吧我终于解决了！

以下是这种特殊情况的步骤:

Here are the steps for this special case:

1. SELECT id 和所需的列.请注意，这要求两列 not 都包含值(我还没有用包含值的两列进行测试，无论是否相同).

1. SELECT the id and the desired columns. Note that this expects the columns to both not contain values (I haven't tested it with both columns containing a value, whether the same or not).

# query1
SELECT id, col1 & col2 AS merged
FROM table_name;

为了安全起见，将之前的查询结果插入到临时表中(遇到错误最好保存).

Insert the results of the query before into a temporary table for security (better save it that encounter errors).

# query2
SELECT * INTO temp
FROM query1
WHERE FirstOfVal;

将临时表的内容插入到主表中.

Insert the contents of the temporary table into the main table.

UPDATE table_name
    INNER JOIN temp ON table_name.id = temp.id
    SET table_name.merged = [temp].[merged];

您现在将合并的列添加到源表中.

You now have the merged columns into your source table.

这篇关于合并行代码返回空内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！