将没有重复项(Distinct)的不同列联接/合并到新表中

Question

尝试了几个小时，但未能找到有效的解决方案。我正在使用MariaDB。我有一个包含数百万行的表(MySourceTable(，我希望在其中从特定列中获取唯一的单元格值，并将它们复制到一个新表中。

COL_A  COL_B  COL_C  COL_D  COL_E
1      11     a      ab     a1  
2      12     a      cd     a2  
3      13     a      ab     a3  
1      14     b      ab     a2  
2      12     c      ef     a5  
4      15     d      gh     a1

新的内容应该是这样的：

COL_A   COL_B  COL_C  COL_D  COL_E
1      11     a      ab     a1  
2      12     b      cd     a2  
3      13     c      ef     a3  
4      14     d      gh     a5  
15 

是的——加入后，这些列不再有任何关系。需要在新的目标表中使用此特定列的唯一行，以便以后与DataTables SearchPanes筛选器一起使用。

编辑：这就是数据表搜索窗格期望过滤器值的方式。如果我不将列值区分到一个新的唯一表中，searchPanes必须在每个页面上刷新到几十万行，才能获得值。

到目前为止我尝试了什么：

DROP TABLE IF EXISTS col_names;
CREATE Table col_names 
(
/*ID MEDIUMINT NOT NULL AUTO_INCREMENT, */
ID MEDIUMINT NOT NULL Auto_Increment, 
COL_A TINYTEXT, 
COL_B TINYTEXT, 
COL_C TINYTEXT, 
COL_D TINYTEXT, 
COL_E TINYTEXT
PRIMARY KEY (ID)
);
INSERT INTO col_names (COL_A)
Select Distinct Source_A AS COL_A FROM MySourceTable;
INSERT INTO col_names (COL_B)
Select Distinct Source_B AS COL_B FROM MySourceTable;
INSERT INTO col_names (COL_C)
Select Distinct Source_C AS COL_C FROM MySourceTable;
INSERT INTO col_names (COL_D)
Select Distinct Source_D AS COL_D FROM MySourceTable;
INSERT INTO col_names (COL_E)
Select Distinct Source_E AS COL_E FROM MySourceTable;
SELECT * FROM col_names
ORDER BY COL_A, COL_B Desc; 

结果并不是我所期望的。我想，我必须与交叉申请，加入，工会等合作。尝试过不同的事情，但失败了很多次。

Answer 1

您可以使用row_number()、union all和聚合：

select max(col_a), max(col_b), . . .
from ((select row_number() over (order by col_a) as seqnum,
col_a, null as col_b, null as col_c, null as col_d, null as col_e
from t
group by col_a
) union all
(select row_number() over (order by col_b) as seqnum,
null as col_a, col_b, null as col_c, null as col_d, null as col_e
from t
group by col_b
) union all
. . .
) x
group by seqnum;

Answer 2

将您的需求更改为标准化输出；两列-column_name和column_value。然后，预期结果中的每个单元格都成为规范化结构中的一行。这更符合sql，更灵活，如果一列的值比其他列多，则不会有奇怪的行为，等等。

SELECT
DISTINCT
lookup.*
FROM
MySourceTable
CROSS APPLY
(
SELECT 'COL_A', MySourceTable.Source_A
UNION ALL
SELECT 'COL_B', MySourceTable.Source_B
UNION ALL
etc, etc
)
AS lookup(column_name, column_value)

或者。。。

SELECT DISTINCT 'COL_A' AS column_name, Source_A AS column_value FROM MySourceTable
UNION ALL
SELECT DISTINCT 'COL_B' AS column_name, Source_B AS column_value FROM MySourceTable
UNION ALL
etc, etc