1. 首页
  2. 问题
  3. 将多行合并为一
admin

将多行合并为一

sql

我在PostgreSQL中有一个数据库结构，看起来像这样：

DROP TABLE IF EXISTS  medium  CASCADE;
DROP TABLE IF EXISTS  works   CASCADE;
DROP DOMAIN IF EXISTS nameVal CASCADE;
DROP DOMAIN IF EXISTS numID   CASCADE;
DROP DOMAIN IF EXISTS alphaID CASCADE;

CREATE DOMAIN alphaID   AS VARCHAR(10);
CREATE DOMAIN numID     AS INT;
CREATE DOMAIN nameVal   AS VARCHAR(40);

CREATE TABLE works (
   w_alphaID    alphaID     NOT NULL,
   w_numID      numID       NOT NULL,
   w_title      nameVal     NOT NULL,
   PRIMARY KEY(w_alphaID,w_numID));


CREATE TABLE medium (
   m_alphaID    alphaID     NOT NULL,
   m_numID      numID       NOT NULL,
   m_title      nameVal     NOT NULL,
   FOREIGN KEY(m_alphaID,m_numID) REFERENCES 
      works ON UPDATE CASCADE ON DELETE CASCADE);

INSERT INTO works VALUES('AB',1,'Sunset'),
                        ('CD',2,'Beach'),
                        ('EF',3,'Flower');

INSERT INTO medium VALUES('AB',1,'Wood'),
                         ('AB',1,'Oil'),
                         ('CD',2,'Canvas'),
                         ('CD',2,'Oil'),
                         ('CD',2,'Bronze'),
                         ('EF',3,'Paper'),
                         ('EF',3,'Pencil');
SELECT * FROM works;
SELECT * FROM medium;

SELECT w_alphaID AS alphaID, w_numID AS numID, w_title AS
       Name_of_work, m_title AS Material_used 
     FROM works, medium WHERE 
       works.w_alphaID = medium.m_alphaID 
       AND works.w_numID = medium.m_numID;

输出看起来像这样：

 w_alphaid | w_numid | w_title 
-----------+---------+---------
 AB        |       1 | Sunset
 CD        |       2 | Beach
 EF        |       3 | Flower
(3 rows)

 m_alphaid | m_numid | m_title 
-----------+---------+---------
 AB        |       1 | Wood
 AB        |       1 | Oil
 CD        |       2 | Canvas
 CD        |       2 | Oil
 CD        |       2 | Bronze
 EF        |       3 | Paper
 EF        |       3 | Pencil
(7 rows)

 alphaid | numid | name_of_work | material_used 
---------+-------+--------------+---------------
 AB      |     1 | Sunset       | Wood
 AB      |     1 | Sunset       | Oil
 CD      |     2 | Beach        | Canvas
 CD      |     2 | Beach        | Oil
 CD      |     2 | Beach        | Bronze
 EF      |     3 | Flower       | Paper
 EF      |     3 | Flower       | Pencil
(7 rows)

现在我的问题是我应该使用哪种查询来使最后一条SELECT语句的格式 看起来像这样：

 alphaid | numid | name_of_work | material_used_1 | material_used_2 | material_used_3 
---------+-------+--------------+-----------------+-----------------+---------------
 AB      |     1 | Sunset       | Wood            | Oil             |
 CD      |     2 | Beach        | Canvas          | Oil             | Bronze
 EF      |     3 | Flower       | Paper           | Pencil          |
(3 rows)

我研究了使用方法，string_agg()但是将值放入一个单元格中，但是我希望为每个值都使用一个单独的单元格。我尝试使用join来查看是否可以实现这样的输出，但到目前为止没有成功。感谢您抽出宝贵的时间来研究这个问题。


阅读 129

收藏
2021-06-07

共1个答案

admin

使用更简单的模式会更简单：

  • 没有域类型（目的是什么？）
  • 在表中添加实际的PK medium
  • 而是使用代理PK（一serial列）代替两种域类型上的多列PK和FK。
    或者至少对具有相同内容的列使用相同（更简单）的列名：alpha_id代替m_alphaIDandw_alphaID等等。

顺便说一句，这里是 按原样 设置的解决方案：

真正的crosstab()解决方案

您的crosstab()查询有几个特定的​​困难：

  • 没有任何单一列可以用作 row_name 。
  • 多个额外的列。
  • 没有 类别 列。
  • 没有定义值的顺序（因此我改用任意顺序）。

基础知识（ 请先阅读！ ）：

对于您的特殊情况：

解决方案：

SELECT alphaid, numid, name_of_work, material_1, material_2, material_3
FROM   crosstab(
  'SELECT rn, w.alphaid, w.numid, w.name_of_work
        , row_number() OVER (PARTITION BY rn) AS mat_nr  -- order undefined!
        , m_title AS Material_used 
   FROM  (
      SELECT w_alphaID AS alphaid, w_numID AS numid, w_title AS name_of_work
           , row_number() OVER (ORDER BY w_alphaID, w_numID) AS rn
       FROM  works
      ) w
   JOIN   medium m ON w.alphaid = m.m_alphaID 
                  AND w.numid   = m.m_numID
   ORDER  BY rn, mat_nr'
 , 'VALUES (1), (2), (3)'  -- add more ...
)
 AS ct (
    rn bigint, alphaid text, numid int, name_of_work text
  , material_1 text, material_2 text, material_3 text  -- add more ...
   );

标准SQL的可怜人的交叉表

如果无法安装附加模块tablefunc或顶级性能并不重要，则此简单查询将执行相同的操作，但速度较慢：

SELECT w_alphaid AS alphaid, w_numid AS numid, w_title AS name_of_work
     , arr[1] AS material_used_1
     , arr[2] AS material_used_2
     , arr[3] AS material_used_3 -- add more?
FROM   works w
LEFT  JOIN (
   SELECT m_alphaid, m_numid, array_agg(m_title::text) AS arr
   FROM   medium
   GROUP  BY m_alphaid, m_numid
   ) m ON w.w_alphaid = m.m_alphaid 
      AND w.w_numid   = m.m_numid;

  • 强制转换为text（或varchar…）是必需的，因为您的自定义域没有预定义的数组类型。或者，您可以定义缺少的数组类型。

  • 与上面的一个细微差别：使用LEFT JOIN此处而不是仅JOIN保留works完全 没有 相关材料的行medium。

  • 由于返回了整个表，因此在加入行medium 之前 汇总行比较便宜。对于较小的选择，首先加入 然后进行汇总 可能会比较便宜。

2021-06-07