英文:
How to union two tables using only their shared columns
问题
迄今为止,我已经使用了一个CTE来获取共享列的列表。问题是,不能在选择语句中使用该列表。
Create table churn_telco as (
WITH cols AS (
SELECT Column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_Name IN (
select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'churn'
)
and table_Name = 'telco'
)
SELECT (SELECT column_name FROM cols)
FROM telco
UNION ALL
Select (SELECT column_name FROM cols)
FROM churn
)
英文:
So far I've used a CTE to get a list of shared columns. The problem is is cant use the list in the select statement.
Create table churn_telco as (
WITH cols AS (
SELECT Column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_Name IN (
select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'churn'
)
and table_Name = 'telco'
)
SELECT (SELECT column_name FROM cols)
FROM telco
UNION ALL
Select (SELECT column_name FROM cols)
FROM churn
)
答案1
得分: -1
不可能使用纯SQL执行动态SQL来创建表格。以下的PL/pgSQL代码块将根据两个源表的并集创建一个新表格:
DO LANGUAGE plpgsql
$block$
DECLARE
column_list text;
BEGIN
SELECT string_agg(format('%I', c1.column_name), ', ' ORDER BY c1.ordinal_position)
INTO column_list
FROM information_schema.columns c1
JOIN information_schema.columns c2
ON c1.column_name = c2.column_name
WHERE c1.table_schema = 'public'
AND c1.table_name = 'telco'
AND c2.table_schema = 'public'
AND c2.table_name = 'churn';
EXECUTE FORMAT($$CREATE TABLE churn_telco AS (SELECT %1$s FROM telco UNION ALL SELECT %1$s FROM churn)$$, column_list);
END
$block$;
英文:
It's not possible to execute dynamic SQL to create tables using pure SQL. The following PL/pgSQL block will create a new table from the union of the two source tables:
DO LANGUAGE plpgsql
$block$
DECLARE
column_list text;
BEGIN
SELECT string_agg(format('%I', c1.column_name), ', ' ORDER BY c1.ordinal_position)
INTO column_list
FROM information_schema.columns c1
JOIN information_schema.columns c2
ON c1.column_name = c2.column_name
WHERE c1.table_schema = 'public'
AND c1.table_name = 'telco'
AND c2.table_schema = 'public'
AND c2.table_name = 'churn';
EXECUTE FORMAT($$CREATE TABLE churn_telco AS (SELECT %1$s FROM telco UNION ALL SELECT %1$s FROM churn)$$, column_list);
END
$block$;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论