postgresql行专列_sql

概述问：怎么分页&&按条件&&按顺序&&姓名不重复查出数据？答：其实就是行转列，那么，postgresql怎么进行转列呢，百度了下，大概有三种写法写法1 group by + sum + case when select name,sum(case when zbfm=‘年龄‘ then value else 0 end) as 年龄,sum(case when zbfm=‘身高‘

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

答：其实就是行转列，那么，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 namehaving name like ‘%1‘ and length(name)=4 order by 年龄 desc

写法2 用postgresql的crosstab交叉函数

crosstab(unknown,unknown) does not exist

select * fromcrosstab(
‘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

写法3 group by + string_agg + split_part(分组，行转列，字符切割)

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

group by + string_agg

select name,‘) from testgroup by name having name like ‘%1‘ and length(name)=4

其他建表语句

CREATE table test(  ID serial NOT NulL,value integer,name character varying,zbfm character varying,CONSTRAINT pkey PRIMARY KEY (ID))

插入数据(python)

import psycopg2from random import randomconn = 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,value) values‘        for j in range(100):            for name in names:                for zbfm in zbfms:                    sqlstr += "(‘%s‘,‘%s‘,%d),"%(name+str(i*100+j),int(100*random()))        cur.execute(sqlstr[:-1])        conn.commit()        print(i)if __name__ == ‘__main__‘:    insertData()    selectData()