Sql: 将行转为列[英] Sql: Transposing rows into columns

本文是小编为大家收集整理的关于Sql: 将行转为列的处理方法,想解了Sql: 将行转为列的问题怎么解决?Sql: 将行转为列问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

考虑下面的示例,其中我有一个包含人员记录的 Person 表和一个包含链接到人员的可选属性的 PersonAttribute 表:

表格:人

ID    Name
1     Joe Bloggs
2     Jane Doe

表 PersonAttribute

PersonId  Key         Value
1         Age         27            
2         HairColor   Brown

如何编写一个查询来返回所有具有属性的人,就好像他们是列一样?我需要的结果集是:

ID    Name        Age    HairColor
1     Joe Bloggs  27     
2     Jane Doe           Brown

所以本质上,我需要编写一个查询,获取所有具有所有唯一属性键的人员记录,并将其转换为具有每个人员记录值的列.

请注意,PersonAttribute 表上的主键是 PersonID 和 Key 组合,因此我们不会有特定键和人员的重复条目.

显然我可以将 Age 和 HairColor 添加为 Person 表中的字段,而不使用 PersonAttribute表,但这只是说明问题的一个例子.实际上,我有大量的自定义属性,这些属性因不同的人员记录而有很大差异,因此这样做是不切实际的.

推荐答案

我不能谈论 MySQL,但是在 PostgreSQL 中你可以使用 tablefunc 模块:

CREATE OR REPLACE VIEW PersonAttributePivot AS
    SELECT PersonId AS ID, Age, HairColor
    FROM crosstab
    (
       'SELECT PersonId, Key, Value FROM PersonAttribute',
       'SELECT DISTINCT Key FROM PersonAttribute ORDER BY Key'
    )
    AS
    (
        PersonId integer,
        Age text,
        HairColor text
    );

加入查询:

SELECT id, name, age, haircolor
FROM Person JOIN PersonAttributePivot USING(id)
ORDER BY id;

想要的结果:

 id |    name    | age | haircolor 
----+------------+-----+-----------
  1 | Joe Bloggs | 27  | 
  2 | Jane Doe   |     | Brown
(2 rows)

如您所见,我在 PersonAttributePivot 视图中放置了明确的列列表.我不知道任何带有隐式列列表的"自动枢轴"创建方式.

编辑:

对于 huge 列列表(假设始终为 text 类型)作为一种解决方法,我看到了这种很少修改的方法:

动态类型创建(这里基于 Java):

Class.forName("org.postgresql.Driver");
Connection c =
        DriverManager.getConnection("jdbc:postgresql://localhost/postgres", "postgres", "12345");
Statement s = c.createStatement();
ResultSet rs = s.executeQuery("SELECT DISTINCT Key FROM PersonAttribute ORDER BY Key");
List<String> columns = new ArrayList<String>();

while (rs.next())
    columns.add(rs.getString(1));

System.out.println("CREATE TYPE PersonAttributePivotType AS (");
System.out.println("\tPersonId integer,");
for (int i = 0; i < columns.size(); ++i)
{
    System.out.print("\t" + columns.get(i) + " text");
    if (i != columns.size() - 1)
        System.out.print(",");
    System.out.println();
}
System.out.println(");");

结果:

CREATE TYPE PersonAttributePivotType AS (
    PersonId integer,
    Age text,
    HairColor text
);

函数封装:

CREATE OR REPLACE FUNCTION crosstabPersonAttribute(text, text)
    RETURNS setof PersonAttributePivotType
    AS '$libdir/tablefunc','crosstab_hash' LANGUAGE C STABLE STRICT;

自动创建视图:

CREATE OR REPLACE VIEW PersonAttributePivot AS
    SELECT * FROM crosstabPersonAttribute
    (
       'SELECT PersonId, Key, Value FROM PersonAttribute',
       'SELECT DISTINCT Key FROM PersonAttribute ORDER BY Key'
    );

结果:

TABLE PersonAttributePivot;
 personid | age | haircolor
----------+-----+-----------
        1 | 27  |
        2 |     | Brown
(2 rows)

本文地址:https://www.itbaoku.cn/post/1764057.html