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

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

How to union two tables using only their shared columns

问题

迄今为止,我已经使用了一个CTE来获取共享列的列表。问题是,不能在选择语句中使用该列表。

  1. Create table churn_telco as (
  2. WITH cols AS (
  3. SELECT Column_Name
  4. FROM INFORMATION_SCHEMA.COLUMNS
  5. WHERE column_Name IN (
  6. select COLUMN_NAME
  7. from INFORMATION_SCHEMA.COLUMNS
  8. where TABLE_NAME = 'churn'
  9. )
  10. and table_Name = 'telco'
  11. )
  12. SELECT (SELECT column_name FROM cols)
  13. FROM telco
  14. UNION ALL
  15. Select (SELECT column_name FROM cols)
  16. FROM churn
  17. )
英文:

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.

  1. Create table churn_telco as (
  2. WITH cols AS (
  3. SELECT Column_Name
  4. FROM INFORMATION_SCHEMA.COLUMNS
  5. WHERE column_Name IN (
  6. select COLUMN_NAME
  7. from INFORMATION_SCHEMA.COLUMNS
  8. where TABLE_NAME = 'churn'
  9. )
  10. and table_Name = 'telco'
  11. )
  12. SELECT (SELECT column_name FROM cols)
  13. FROM telco
  14. UNION ALL
  15. Select (SELECT column_name FROM cols)
  16. FROM churn
  17. )

答案1

得分: -1

不可能使用纯SQL执行动态SQL来创建表格。以下的PL/pgSQL代码块将根据两个源表的并集创建一个新表格:

  1. DO LANGUAGE plpgsql
  2. $block$
  3. DECLARE
  4. column_list text;
  5. BEGIN
  6. SELECT string_agg(format('%I', c1.column_name), ', ' ORDER BY c1.ordinal_position)
  7. INTO column_list
  8. FROM information_schema.columns c1
  9. JOIN information_schema.columns c2
  10. ON c1.column_name = c2.column_name
  11. WHERE c1.table_schema = 'public'
  12. AND c1.table_name = 'telco'
  13. AND c2.table_schema = 'public'
  14. AND c2.table_name = 'churn';
  15. EXECUTE FORMAT($$CREATE TABLE churn_telco AS (SELECT %1$s FROM telco UNION ALL SELECT %1$s FROM churn)$$, column_list);
  16. END
  17. $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:

  1. DO LANGUAGE plpgsql
  2. $block$
  3. DECLARE
  4. column_list text;
  5. BEGIN
  6. SELECT string_agg(format('%I', c1.column_name), ', ' ORDER BY c1.ordinal_position)
  7. INTO column_list
  8. FROM information_schema.columns c1
  9. JOIN information_schema.columns c2
  10. ON c1.column_name = c2.column_name
  11. WHERE c1.table_schema = 'public'
  12. AND c1.table_name = 'telco'
  13. AND c2.table_schema = 'public'
  14. AND c2.table_name = 'churn';
  15. EXECUTE FORMAT($$CREATE TABLE churn_telco AS (SELECT %1$s FROM telco UNION ALL SELECT %1$s FROM churn)$$, column_list);
  16. END
  17. $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:

确定