postgresql行转列

旧城等待， 2023-09-24 21:07 59阅读 0赞

![de97aa25c6dbd7b22145d0e3f8bd80fd.png][]

问：怎么**分页**&&**按条件**&&**按顺序**&&**姓名****不重复**查出数据？

答：其实就是行转列，那么，postgresql怎么进行转列呢，百度了下，大概有三种写法

## 写法1 ##

group by + sum + case when

select name,
    sum(case when zbfm='年龄' then value else 0 end) as 年龄,
    sum(case when zbfm='身高' then value else 0 end) as 身高,
    sum(case when zbfm='体重' then value else 0 end) as 体重
    from test group by name
    having name like '%1' and length(name)=4 order by 年龄 desc

![f450d265f8817e8a79a8a58c1e0dd6d4.png][]

![cc9f47a94584143fc649b84b79a6db60.png][]

## 写法2 ##

用postgresql的crosstab交叉函数

[crosstab(unknown, unknown) does not exist][crosstab_unknown_ unknown_ does not exist]

select * from
    crosstab(
    'select name,zbfm,value from test where name like ''%1'' and length(name)=4',$$values('年龄'), ('身高'), ('体重')$$)
    as score(name text, 年龄 int, 身高 int, 体重 int)
    order by 年龄 desc

![b1068a09bced013d968418f18005bd5e.png][]

![f6fe84d2d29622ff62107a97b25f00de.png][]

## 写法3 ##

group by + string\_agg + split\_part(分组，行转列，字符切割)

select name, 
    split_part(split_part(temp,',',1),':',2) as 年龄,
    split_part(split_part(temp,',',2),':',2) as 身高,
    split_part(split_part(temp,',',3),':',2) as 体重
    from(
    select name, string_agg(zbfm||':'||value,',') as temp from test
    group by name 
    having name like '%1' and length(name)=4
    ) as t order by 年龄 desc

![fbba5a4650db053d2dfd812f1ceead51.png][]

![a22d10ae0f17410c3133491428b38538.png][]

group by + string\_agg

select name, string_agg(zbfm||':'||value,',') from test
    group by name 
    having name like '%1' and length(name)=4

![836fd8b3310d841e70596a6088292367.png][]

##  ##

## 其他 ##

建表语句

CREATE TABLE test
    (
      id serial NOT NULL,
      value integer,
      name character varying,
      zbfm character varying,
      CONSTRAINT pkey PRIMARY KEY (id)
    )

插入数据(python)

import psycopg2
    from random import random
    conn = psycopg2.connect(database="postgres", user="postgres", password="password", host="ip", port="port")
    cur = conn.cursor()
    
    def insertData():
        names = ['路人甲', '王尼玛', '唐马儒']
        zbfms = ['年龄', '身高', '体重']
    
        for i in range(100):
            sqlstr = 'insert into test(name, zbfm, value) values'
            for j in range(100):
                for name in names:
                    for zbfm in zbfms:
                        sqlstr += "('%s','%s',%d),"%(name+str(i*100+j),zbfm,int(100*random()))
            cur.execute(sqlstr[:-1])
            conn.commit()
            print(i)
    
    if __name__ == '__main__':
        insertData()
        selectData()

## 参考 ##

[PostgreSQL 实现交叉表（行列转换）的五种方法][PostgreSQL]

Become a Linux Programmer

原文链接：[https://www.cnblogs.com/lurenjia1994/p/9535899.html][https_www.cnblogs.com_lurenjia1994_p_9535899.html]

[de97aa25c6dbd7b22145d0e3f8bd80fd.png]: https://img-blog.csdnimg.cn/img_convert/de97aa25c6dbd7b22145d0e3f8bd80fd.png
[f450d265f8817e8a79a8a58c1e0dd6d4.png]: https://img-blog.csdnimg.cn/img_convert/f450d265f8817e8a79a8a58c1e0dd6d4.png
[cc9f47a94584143fc649b84b79a6db60.png]: https://img-blog.csdnimg.cn/img_convert/cc9f47a94584143fc649b84b79a6db60.png
[crosstab_unknown_ unknown_ does not exist]: https://www.cnblogs.com/lurenjia1994/p/9535663.html
[b1068a09bced013d968418f18005bd5e.png]: https://img-blog.csdnimg.cn/img_convert/b1068a09bced013d968418f18005bd5e.png
[f6fe84d2d29622ff62107a97b25f00de.png]: https://img-blog.csdnimg.cn/img_convert/f6fe84d2d29622ff62107a97b25f00de.png
[fbba5a4650db053d2dfd812f1ceead51.png]: https://img-blog.csdnimg.cn/img_convert/fbba5a4650db053d2dfd812f1ceead51.png
[a22d10ae0f17410c3133491428b38538.png]: https://img-blog.csdnimg.cn/img_convert/a22d10ae0f17410c3133491428b38538.png
[836fd8b3310d841e70596a6088292367.png]: https://img-blog.csdnimg.cn/img_convert/836fd8b3310d841e70596a6088292367.png
[PostgreSQL]: https://blog.csdn.net/a258831020/article/details/48446213
[https_www.cnblogs.com_lurenjia1994_p_9535899.html]: https://www.cnblogs.com/lurenjia1994/p/9535899.html