从2列中获取唯一值合并到1列中

huangapple go评论67阅读模式
英文:

How To Get The Distinct Values From 2 columns Into 1

问题

使用表table1中的数值,我该如何获取这两列的不同值并将它们用于在新表中创建一个新列?

附上了输入数值的示例表格和期望的输出。

我正在使用Snowflake数据库。

英文:

Say I have a table, table1.

And table1 has 2 columns, "Vegetables" and "Fruits"

Using the values from table 1, how can I get the distinct of these 2 columns and use them to create a new column in a new table?

Attached is a sample table of input values, and expected output

I am using snowflake.

从2列中获取唯一值合并到1列中

答案1

得分: 1

像这样吗?

CREATE TABLE table2 (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255)
);

INSERT INTO table2 (name)
SELECT name FROM (
  SELECT vegetables AS name FROM table1
  UNION
  SELECT fruits AS name FROM table1
) AS names
GROUP BY name;

查看这个链接 --> sqlfiddle

英文:

Something like this?

CREATE TABLE table2 (
id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255)
);

INSERT INTO table2 (name)
SELECT name FROM (
  SELECT vegetables AS name FROM table1
  UNION
  SELECT fruits AS name FROM table1
) AS names
GROUP BY name;

Check this out --> sqlfiddle

答案2

得分: 1

SELECT DISTINCT column_name FROM
(
SELECT fruits AS column_name FROM table1
UNION
SELECT vegetables AS column_name FROM table2
) AS distinct_values;

英文:
SELECT DISTINCT column_name FROM
(
  SELECT fruits AS column_name FROM table1
  UNION
  SELECT vegetables AS column_name FROM table2
) AS distinct_values;

huangapple
  • 本文由 发表于 2023年2月24日 09:45:55
  • 转载请务必保留本文链接:https://go.coder-hub.com/75551926.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定