postgresql行专列
问:怎么分页&&按条件&&按顺序&&姓名不重复查出数据?
答:其实就是行转列,那么,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
写法2
用postgresql的crosstab交叉函数
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
写法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
group by + string_agg
select name, string_agg(zbfm||':'||value,',') from test group 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 psycopg2 from random import random conn = psycopg2.connect(database="postgres", user="postgres", password="yess5678", host="119.29.218.235", port="5432") 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()