Mysql 行列轉換

標籤：

　　一、第一種

　　原資料表

　　

　　轉換後

　　

DROP TABLE IF EXISTS tempdynamic;CREATE  TEMPORARY TABLE tempdynamic ( SELECT p.fsPaymentName,sr.fsPaymentId,sh.fsShiftName,SUM(sr.fdReceMoney) as AmtTotal FROM tbsell as sINNER 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=1INNER 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=1WHERE s.fiBillStatus=3AND s.fsShopGUID=‘c5f0d5c9d8b349f493c180bcc22d9ce0‘AND s.fsSellDate=‘2016-01-15‘GROUP BY sr.fsPaymentId,sc.fsShiftId);SET @EE=‘‘;set @str_tmp=‘‘; SELECT   @EE:=CONCAT(@EE,‘SUM(IF(fsShiftName=\‘‘,fsShiftName,‘\‘‘,‘,AmtTotal,0)) AS ‘,fsShiftName,‘,‘) as aa into @str_tmpFROM ( SELECT DISTINCT fsShiftName from  tempdynamic   ) A order 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; EXECUTE stmt ;deallocate prepare stmt;

View Code

　　二、第二種

　　原資料表

　　

　　轉換後

　　

 1 DROP TABLE IF EXISTS tempdynamic; 2 CREATE    TABLE tempdynamic ( 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 as s 5 INNER JOIN tbSellCheck as sc on sc.fsSellNo=s.fsSellNo AND s.fsShopGUID=sc.fsShopGUID 6 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 BY   sc.fsShiftId);11  12 DROP TABLE IF EXISTS tempdynamica;13 CREATE  TEMPORARY TABLE tempdynamica(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   ALL18 SELECT   fsShiftName,‘應收金額‘   AS     科目,應收金額   AS   AmtTotal   FROM   tempdynamic UNION   ALL19 SELECT   fsShiftName,‘實收金額‘   AS     科目,實收金額   AS   AmtTotal   FROM   tempdynamic  20 ORDER BY fsShiftName,科目 DESC) as a );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_tmp26 FROM (27  SELECT DISTINCT fsShiftName from  tempdynamic   28 ) 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 EXECUTE stmt ;32 deallocate prepare stmt;

View Code

 

Mysql 行列轉換