求一个 PostgreSQL 动态行转列的 例子 自己改写成功后 给分 。在线等。
解决方案 »
- 大数据量时,如何提高mysql转换存储引擎的效率?
- xpe操作系统 安装 mysql front 乱码
- 关于mysql++安装的问题
- 大家看看这个SQL语句怎么优化
- mysql命令行无法登陆
- MySql远程连接的问题?
- mysql中是否有这样的功能,能否把昨天的整个数据库还原出来?
- 错误代号:2006 mysql server has gone away
- 数据库分表设计的问题
- 这个是什么错误呢,设置外键时显示ERROR 1022: Can't write; duplicate key in table '#sql-434_2'
- 如何删除元组,包括下级元组。数据库
- 请教 mysqldump 的问题!!!
返回游标
CREATE FUNCTION reffunc2() RETURNS refcursor AS '
declare
sql text;
myrec record;
ref refcursor
begin
sql:='select 姓名 ';
for myrec in select distinct 课程 from tb loop
sql:=sql||$$ , max(case 课程 when '$$||myrec.课程||$$' then 分数 else 0 end) as $$||myrec.课程||;
end loop
sql:=sql||' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
open refr for execute sql;
return ref;
end;BEGIN;
SELECT reffunc2();reffunc2
--------------------
<unnamed cursor 1>
(1 row)FETCH ALL IN "<unnamed cursor 1>";
COMMIT;
就是字符串累加生成SQL语句,再执行
这个例子我早就看过了。
BEGIN;
SELECT reffunc2(); reffunc2
--------------------
<unnamed cursor 1>
(1 row) FETCH ALL IN " <unnamed cursor 1>";
COMMIT;
这个执行的时候 回报错 说找不到" <unnamed cursor 1>"
字段 CustomerId 主键
Cus_Field表 存放自定义字段信息 就是说 用户自己给客户附加自定义的信息
字段 FieldId 主键
FieldName 自定义字段的名称
Cus_CustomerField 表 存放 自定义字段对应每个客户的值
字段 CustomerFieldId 主键 CustomerId 客户Id FieldId 字段Id FieldValue 字段值 我需要得到的结果集的结果是
CustomerId 字段1 字段2 字段3....
"CustomerId" SERIAL,
"CustomerName" VARCHAR(50) NOT NULL,
CONSTRAINT "pk_cus_customer" PRIMARY KEY("CustomerId")
) WITHOUT OIDS;
INSERT INTO "public"."Cus_Customer"("CustomerName") VALUES ('张三');
INSERT INTO "public"."Cus_Customer"("CustomerName") VALUES ('李四');
INSERT INTO "public"."Cus_Customer"("CustomerName") VALUES ('王五');CREATE TABLE "public"."Cus_Field" (
"FieldId" SERIAL,
"FieldName" VARCHAR(50) NOT NULL,
CONSTRAINT "pk_cus_field" PRIMARY KEY("FieldId")
) WITHOUT OIDS;INSERT INTO "public"."Cus_Field"("FieldName") VALUES ('Sex');
INSERT INTO "public"."Cus_Field"("FieldName") VALUES ('Age');
INSERT INTO "public"."Cus_Field"("FieldName") VALUES ('Education');Select * from "Cus_CustomerField"CREATE TABLE "public"."Cus_CustomerField" (
"CustomerFieldId" SERIAL,
"CustomerId" INTEGER NOT NULL,
"FieldId" INTEGER NOT NULL,
"FieldValue" VARCHAR(800) NOT NULL,
CONSTRAINT "pk_cus_customerfield" PRIMARY KEY("CustomerFieldId")
) WITHOUT OIDS;INSERT INTO "public"."Cus_CustomerField"("CustomerId","FieldId","FieldValue")
VALUES (1,1,'男');
INSERT INTO "public"."Cus_CustomerField"("CustomerId","FieldId","FieldValue")
VALUES (1,2,20);
INSERT INTO "public"."Cus_CustomerField"("CustomerId","FieldId","FieldValue")
VALUES (1,3,'本科');
INSERT INTO "public"."Cus_CustomerField"("CustomerId","FieldId","FieldValue")
VALUES (2,1,'男');
INSERT INTO "public"."Cus_CustomerField"("CustomerId","FieldId","FieldValue")
VALUES (2,2,'22');
INSERT INTO "public"."Cus_CustomerField"("CustomerId","FieldId","FieldValue")
VALUES (2,3,'大专');
INSERT INTO "public"."Cus_CustomerField"("CustomerId","FieldId","FieldValue")
VALUES (3,1,'女');
INSERT INTO "public"."Cus_CustomerField"("CustomerId","FieldId","FieldValue")
VALUES (3,2,'20');
INSERT INTO "public"."Cus_CustomerField"("CustomerId","FieldId","FieldValue")
VALUES (3,3,'本科');要得到的结果是
CustomerId Sex Age Education
1 男 20 本科
2 男 22 大专
3 女 20 本科注明: 这里的Sex,Age,Education这些列的数量不确定 需要动态的 谢谢高手帮助
csdn-# RETURNS refcursor AS $$
csdn$# DECLARE
csdn$# sql varchar(1000);
csdn$# curs CURSOR FOR SELECT * FROM "Cus_Field";
csdn$# myrow "Cus_Field"%ROWTYPE;
csdn$# ref refcursor;
csdn$# BEGIN
csdn$# sql := 'select "CustomerId" ';
csdn$# FOR myrow IN curs LOOP
csdn$# sql := sql || ',max(case "FieldId" when ' || myrow."FieldId" || ' then "FieldValue" end) as ' || myrow."FieldName";
csdn$# END LOOP ;
csdn$# sql := sql || ' from "Cus_CustomerField" group by "CustomerId"';
csdn$# open ref for execute sql;
csdn$# RETURN ref;
csdn$# END;
csdn$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
csdn=# BEGIN;
BEGIN
csdn=# SELECT pivot_table();
pivot_table
--------------------
<unnamed portal 1>
(1 row)
csdn=# FETCH ALL IN "<unnamed portal 1>";
CustomerId | sex | age | education
------------+-----+-----+-----------
1 | 男 | 20 | 本科
3 | 女 | 20 | 本科
2 | 男 | 22 | 大专
(3 rows)
csdn=# commit;
服务器游标默认只能在一个事务中存在,事务结束自动销毁.
如果你没用BEGIN开启一个事务,任何一条语句都是一个事务,所以select reffunc2()所建立的游标立马被销毁.
RETURNS refcursor AS
$BODY$declare
sql text;
myrec record;
ref refcursor;
begin
sql:='select "CustomerId" ';
for myrec in select * from "Cus_Field" loop
sql := sql || ',max(case "FieldId" when ' || myrec."FieldId" ||
' then "FieldValue" end) as ' || myrec."FieldName";
end loop ;
sql := sql || ' from "Cus_CustomerField" group by "CustomerId"';
open ref for execute sql;
return ref;
end;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION aa() OWNER TO postgres;调用 :
select aa();
fetch all in "<unnamed portal 36>";
select aa3('dd');
fetch all in aa;CREATE OR REPLACE FUNCTION aa3(refcursor) RETURNS refcursor AS
$BODY$
declare
sql varchar(1000);
myrec record;
ref refcursor;
begin
ref:='aa';
sql:='select "CustomerId" ';
for myrec in select * from "Cus_Field" loop
sql := sql || ',max(case "FieldId" when ' || myrec."FieldId" ||
' then "FieldValue" end) as ' || myrec."FieldName";
end loop ;
sql := sql || ' from "Cus_CustomerField" group by "CustomerId"';
open ref for execute sql;
return ref;
end;
$BODY$
LANGUAGE 'sql' VOLATILE
COST 100;
fetch all in dd1;CREATE OR REPLACE FUNCTION aa4(refcursor) RETURNS refcursor AS $$
declare
sql varchar(1000);
myrec record;
ref refcursor;
begin
--ref:='aa';
sql:='select "CustomerId" ';
for myrec in select * from "Cus_Field" loop
sql := sql || ',max(case "FieldId" when ' || myrec."FieldId" ||
' then "FieldValue" end) as ' || myrec."FieldName";
end loop ;
sql := sql || ' from "Cus_CustomerField" group by "CustomerId"';
open $1 for execute sql;
return $1;
end;
$$ LANGUAGE plpgsql;