主页 / dba / 问题 / 120346
Accepted
Franck Dernoncourt
Asked: 2015-11-07 11:37:52 +0800 CST

根据输入列值选择要插入的列

  • 772

假设有以下两个表（并忽略数据库设计的质量）：

  • 表 1 有列id, sequence_number, value：它包含许多行。
  • 表 2 有列id, seq1, seq2, seq3, seq4, seq5：它是空的。

目标是用表 1 的值填充表 2，这样如果表 1 中的一行是sequence_number = 3；value = 1564.1, 那么在表 2 中应该插入一行seq3 = 1564.1. （除此之外的其他列id应为空）。

如何在 SQL 中做到这一点？我不想对所有 5 个选项进行硬编码（因为在我的实际用例中有超过 50 个。），但我不知道如何根据值来调整应该接收值的列名。

postgresql pivot

2 个回答

  1. 通过使用 CASE 语句..

    INSERT INTO table2 (id, seq1, seq2, seq3, seq4, seq5)
SELECT
  <SOME ID VALUE>,
  CASE WHEN sequence_number = 1 THEN value ELSE NULL END,
  CASE WHEN sequence_number = 2 THEN value ELSE NULL END,
  CASE WHEN sequence_number = 3 THEN value ELSE NULL END,
  CASE WHEN sequence_number = 4 THEN value ELSE NULL END,
  CASE WHEN sequence_number = 5 THEN value ELSE NULL END
FROM table1

    如果您考虑硬编码，那么（据我所知）您唯一的其他选择是 plpgsql 块中的动态 SQL。

    DO $$
DECLARE
  row table1%ROWTYPE;
  colName VARCHAR(10);
BEGIN
  FOR row in SELECT * FROM TABLE1 LOOP
    colName := 'seq' || row.sequence_number;
    EXECUTE '<SQL HERE THAT USES colName FOR INSERT>';
  END LOOP
END;
$$ language plpgsql;
    • 2
  2. Best Answer

    这基本上是交叉制表/交叉表/数据透视表的问题。

    鉴于这些确切的表定义：

    CREATE TABLE tbl1 (
  id int
, sequence_number int
, value numeric
);

CREATE TABLE tbl2 (
  id int
, seq1 numeric
, seq2 numeric
, seq3 numeric
, seq4 numeric
, seq5 numeric
);

    您只需要这个基本的交叉表查询：

    INSERT INTO tbl2
SELECT * FROM  crosstab (
  'TABLE tbl1'
, 'SELECT generate_series(1,5)'
) t(id int, seq1 numeric, seq2 numeric, seq3 numeric, seq4 numeric, seq5 numeric);

    也是我能想到的最快的方法。
    您需要安装附加模块 tablefunc。基本：

    如果您的任何一个表有更多列，请在第一个参数中更明确和/或适应更多列：

    SELECT * FROM  crosstab (
  'SELECT id, sequence_number, value FROM tbl1'  -- optionally ORDER BY
, 'SELECT generate_series(1,7)'
) t(id int, seq1 numeric, seq2 numeric, seq3 numeric, seq4 numeric
          , seq5 numeric, seq6 numeric, seq7 numeric);

    更多自动化

    如果你必须经常这样做，你可以进一步自动化：

    crosstab_n() 使用链接答案中定义的 2 参数形式，查询可以很简单：

    SELECT * FROM crosstab_n('TABLE tbl1', 'SELECT generate_series(1,5)', NULL::tbl2);

    seqN如果您有那么多列，请将 5 替换为 50 。

    • 1

相关问题