如何仅使用它们共享的列合并两个表格。

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

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$;

huangapple
  • 本文由 发表于 2023年7月14日 00:53:19
  • 转载请务必保留本文链接:https://go.coder-hub.com/76681714.html
匿名

发表评论

匿名网友

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

确定