mysql postgresql 转换_Mysql postgresql 行列转换

一、第一种

原数据表

转换后

DROP TABLE IF EXISTStempdynamic;CREATE TEMPORARY TABLEtempdynamic (SELECT p.fsPaymentName,sr.fsPaymentId,sh.fsShiftName,SUM(sr.fdReceMoney) as AmtTotal FROM tbsell assINNER JOIN tbSellReceive as sr ON s.fsSellNo=sr.fsSellNo AND s.fsShopGUID=sr.fsShopGUIDINNER JOIN tbpayment as p on sr.fsPaymentId =p.fsPaymentId and p.fsShopGUID=s.fsShopGUID and p.fiStatus=1

INNER JOIN tbSellCheck as sc on sc.fsSellNo=s.fsSellNo AND s.fsShopGUID=sc.fsShopGUIDINNER JOIN tbshift as sh ON sc.fsShiftId=sh.fsShiftId AND sh.fiStatus=1

WHERE s.fiBillStatus=3

AND s.fsShopGUID='c5f0d5c9d8b349f493c180bcc22d9ce0'

AND s.fsSellDate='2016-01-15'

GROUP BYsr.fsPaymentId,sc.fsShiftId);SET @EE='';set @str_tmp='';SELECT @EE:=CONCAT(@EE,'SUM(IF(fsShiftName=\'',fsShiftName,'\'',',AmtTotal,0)) AS',fsShiftName,',') as aa into @str_tmp

FROM(SELECT DISTINCT fsShiftName fromtempdynamic

) Aorder by length(aa) desc limit 1;SET @QQ=CONCAT('SELECT ifnull(fsPaymentName,\'total\') as 科目名称 ,',@str_tmp,'SUM(AmtTotal) as 合计 FROM tempdynamic Group by fsPaymentName WITH ROLLUP');PREPARE stmt FROM @QQ;EXECUTEstmt ;deallocate prepare stmt;

View Code

二、第二种

原数据表

转换后

1 DROP TABLE IF EXISTStempdynamic;2 CREATE TABLEtempdynamic (3 SELECT sh.fsShiftName,sh.fsShiftId, SUM(s.fdSaleAmt) as 销售金额,SUM(s.fdDiscountAmt) as 折扣金额, SUM(s.fdServiceAmt) as 服务费金额,SUM(s.fdExpAmt) as 应收金额 ,SUM(s.fdRealAmt) as实收金额4 FROM tbsell ass5 INNER JOIN tbSellCheck as sc on sc.fsSellNo=s.fsSellNo AND s.fsShopGUID=sc.fsShopGUID6 INNER JOIN tbshift as sh ON sc.fsShiftId=sh.fsShiftId AND sh.fiStatus=1

7 WHERE s.fiBillStatus=3

8 AND s.fsShopGUID='c5f0d5c9d8b349f493c180bcc22d9ce0'

9 AND s.fsSellDate='2016-01-15'

10 GROUP BYsc.fsShiftId);11

12 DROP TABLE IF EXISTStempdynamica;13 CREATE TEMPORARY TABLEtempdynamica(14 SELECT a.* FROM(15 SELECT fsShiftName,'销售金额' AS 科目,销售金额 AS AmtTotal FROM tempdynamic UNION ALL

16 SELECT fsShiftName,'折扣金额' AS 科目,折扣金额 AS AmtTotal FROM tempdynamic UNION ALL

17 SELECT fsShiftName,'服务费金额' AS 科目,服务费金额 AS AmtTotal FROM tempdynamic UNION ALL

18 SELECT fsShiftName,'应收金额' AS 科目,应收金额 AS AmtTotal FROM tempdynamic UNION ALL

19 SELECT fsShiftName,'实收金额' AS 科目,实收金额 AS AmtTotal FROMtempdynamic20 ORDER BY fsShiftName,科目 DESC) asa );21

22

23 SET @EE='';24 set @str_tmp='';25 SELECT @EE:=CONCAT(@EE,'SUM(IF(fsShiftName=\'',fsShiftName,'\'',',AmtTotal ,0)) AS',fsShiftName,',') as aa into @str_tmp

26 FROM(27 SELECT DISTINCT fsShiftName fromtempdynamic28 ) A order by length(aa) desc limit 1;29 SET @QQ=CONCAT('SELECT ifnull(科目,\'total\') as 科目名称,',@str_tmp,'sum(AmtTotal) as 合计 FROM tempdynamica Group by 科目 WITH ROLLUP');30 PREPARE stmt FROM @QQ;31 EXECUTEstmt ;32 deallocate prepare stmt;

View Code

postgresql

原数据：

转换后：

sql:

CREATE OR REPLACE FUNCTION "public"."test"()

RETURNS "pg_catalog"."void" AS $BODY$

DECLARE

dynsql1 VARCHAR ;

dynsql2 VARCHAR ;

dynsql3 VARCHAR ;

BEGIN

drop table if exists temp_sell;

dynsql1='CREATE TEMP TABLE temp_sell AS SELECT

bd.area_id,

bd.area_name,

bd.sell_date,

SUM (

COALESCE (bss.original_amt, 0)

) AS original_amt

FROM

bill_detail AS bd

INNER JOIN bill_settlement_summary AS bss ON bd.bill_id = bss.bill_id

WHERE

bd.sell_date BETWEEN ''2018-01-01''

AND ''2018-01-30''

AND bd.shop_guid=''143174''

-- AND area_id=''''

-- AND msection_id=''''

GROUP BY

bd.sell_date,

bd.area_id,

bd.area_name;';

EXECUTE dynsql1;

dynsql2 = '';

dynsql3 = '';

drop table if exists temp_sell_1;

CREATE TEMP TABLE temp_sell_1 AS

SELECT concat('SUM(CASE WHEN sell_date=''',sell_date,'''','then original_amt else 0 end) AS "',sell_date,'"') AS aa

FROM

(SELECT DISTINCT

sell_date

FROM

temp_sell) A

ORDER BY sell_date ASC ;

SELECT string_agg(aa, ',') INTO dynsql2 from temp_sell_1;

RAISE NOTICE 'dynsql2==[%]', dynsql2 ;

dynsql3 = CONCAT(

'SELECT area_id as 区域id,area_name as 区域名称,' ,dynsql2,

',sum(original_amt) 汇总 FROM temp_sell Group By area_id,area_name;'

);

RAISE NOTICE 'dynsql3==[%]', dynsql3 ;

END ;

$BODY$

LANGUAGE 'plpgsql' VOLATILE COST 100

;

SELECT test();

SELECT area_id as 区域id,area_name as 区域名称,SUM(CASE WHEN sell_date='2018-01-12'then original_amt else 0 end) AS "2018-01-12",SUM(CASE WHEN sell_date='2018-01-18'then original_amt else 0 end) AS "2018-01-18",SUM(CASE WHEN sell_date='2018-01-19'then original_amt else 0 end) AS "2018-01-19",SUM(CASE WHEN sell_date='2018-01-25'then original_amt else 0 end) AS "2018-01-25",sum(original_amt) 汇总 FROM temp_sell Group By area_id,area_name;

第二种方案：

参考： http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/

使用函数如下：

CREATE OR REPLACE FUNCTION "public"."pivotcode"("tablename" varchar, "rowc" varchar, "rowctype" varchar, "groupby" varchar, "colc" varchar, "cellc" varchar, "celldatatype" varchar)RETURNS "pg_catalog"."void" AS$BODY$DECLARE rec record ; dynsql1 VARCHAR ; dynsql2 VARCHAR ; countrows INT; columnlist VARCHAR;BEGIN

drop table if existstemp_sell_result;

dynsql1= 'select count(1) from' || tablename || ';';EXECUTE dynsql1 INTOcountrows ;IF countrows=0 THENdynsql1='CREATE TEMP TABLE temp_sell_result AS select * from' || tablename || ';';EXECUTEdynsql1;ELSE

--1. retrieve list of column names.

dynsql1 = 'select string_agg(distinct''"''||' || colc || '||''"' || celldatatype || ''','',''order by''"''||' || colc || '||''"' || celldatatype || ''') from' || tablename || ';';--RAISE NOTICE '%', dynsql1 ;

EXECUTE dynsql1 INTOcolumnlist ;--2. set up the crosstab query

dynsql2= 'CREATE TEMP TABLE temp_sell_result AS select * from crosstab (''select' || rowc || ',' || colc || ',' || cellc || 'from' || tablename || 'group by'||groupby||'order by'||groupby||''',''select distinct' || colc || 'from' || tablename || 'order by 1'')

as newtable (' || rowctype || ',' || columnlist || ');';EXECUTEdynsql2 ;END IF;END;

$BODY$

LANGUAGE'plpgsql' VOLATILE COST 100;ALTER FUNCTION "public"."pivotcode"("tablename" varchar, "rowc" varchar, "rowctype" varchar, "groupby" varchar, "colc" varchar, "cellc" varchar, "celldatatype" varchar) OWNER TO "winposdev";

View Code