mysql行转列
案例一丶
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 SET @sql = NULL; 2 SELECT 3 GROUP_CONCAT(DISTINCT 4 CONCAT( 5 'MAX(IF(c.coursenm = ''', 6 c.coursenm, 7 ''', s.scores, 0)) AS ''', 8 c.coursenm, '''' 9 ) 10 ) INTO @sql 11 FROM courses c; 12 13 SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql, 14 ' From Student st 15 Left Join score s On st.stuid = s.stuid 16 Left Join courses c On c.courseno = s.courseno 17 Group by st.stuid'); 18 PREPARE stmt FROM @sql; 19 EXECUTE stmt; 20 DEALLOCATE PREPARE stmt;
案例二丶
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 CREATE DEFINER=`cmsuser`@`%` PROCEDURE `proc_zz`( 2 IN _PlanArea varchar(50), 3 IN _ProjectId varchar(50), 4 IN _FloorNumber varchar(50), 5 IN _FormatsName varchar(50), 6 IN _TwoFormatsName varchar(50), 7 IN _BunkNo varchar(50), 8 IN _BrandAbbreviation varchar(50), 9 IN _BusinessManager varchar(50), 10 IN _keyWord varchar(50) 11 ) 12 begin 13 14 SET @sql = NULL; 15 SET @sqlwhere =''; 16 17 SELECT 18 GROUP_CONCAT(DISTINCT 19 CONCAT( 20 'MAX(IF(fl.LicenceName = ''', 21 fl.LicenceName, 22 ''', lm.LicenseValidityDate, null)) AS ', 23 fl.LicenceName 24 ) 25 ) INTO @sql 26 FROM he_operating_formatsandlicencerelational fl; 27 28 if _PlanArea is not null and _PlanArea !='' then 29 SET @sqlwhere=CONCAT(@sqlwhere," ","and s.PlanArea=","'",_PlanArea,"'"); 30 end if; 31 if _ProjectId is not null and _ProjectId !='' then 32 SET @sqlwhere=CONCAT(@sqlwhere," ","and s.ProjectId=","'",_ProjectId,"'"); 33 end if; 34 if _FloorNumber is not null and _FloorNumber !='' then 35 SET @sqlwhere=CONCAT(@sqlwhere," ","and s.FloorNumber=","'",_FloorNumber,"'"); 36 end if; 37 if _FormatsName is not null and _FormatsName !='' then 38 SET @sqlwhere=CONCAT(@sqlwhere," ","and s.FormatsName=","'",_FormatsName,"'"); 39 end if; 40 if _TwoFormatsName is not null and _TwoFormatsName !='' then 41 SET @sqlwhere=CONCAT(@sqlwhere," ","and s.TwoFormatsName=","'",_TwoFormatsName,"'"); 42 end if; 43 if _BunkNo is not null and _BunkNo !='' then 44 SET @sqlwhere=CONCAT(@sqlwhere," ","and s.BunkNo=","'",_BunkNo,"'"); 45 end if; 46 if _BrandAbbreviation is not null and _BrandAbbreviation !='' then 47 SET @sqlwhere=CONCAT(@sqlwhere," ","and s.BrandAbbreviation=","'",_BrandAbbreviation,"'"); 48 end if; 49 if _BusinessManager is not null and _BusinessManager !='' then 50 SET @sqlwhere=CONCAT(@sqlwhere," ","and s.BusinessManager=","'",_BusinessManager,"'"); 51 end if; 52 if _keyWord is not null and _keyWord !='' then 53 SET @sqlwhere=CONCAT(@sqlwhere," ", 54 "and s.PlanArea=","'",_keyWord,"'", 55 "or s.PlanArea=","'",_keyWord,"'", 56 "or s.PlanArea=","'",_keyWord,"'" 57 ); 58 end if; 59 60 SET @sql = CONCAT('SELECT s.StoreNo,s.PlanArea,s.ProjectName,s.FloorNumber,s.FormatsCode,s.FormatsName,s.TwoFormatsName 61 , s.BunkNo,s.BrandAbbreviation,s.BusinessManager,s.StoreManager 62 , s.StoreManagerPhone, ', @sql, ' FROM he_merchants_store s 63 LEFT JOIN he_operating_licencemanageinfo AS lm ON s.StoreNo = lm.StoreNo 64 LEFT JOIN he_operating_formatsandlicencerelational fl on s.FormatsCode=fl.FormatsCode 65 WHERE 1=1',@sqlwhere,' GROUP BY s.StoreNo,s.FormatsCode'); 66 67 prepare stmt from @sql; 68 execute stmt; 69 deallocate prepare stmt; 70 end
作者:chenze 出处:https://www.cnblogs.com/chenze-Index/ 本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。 如果文中有什么错误,欢迎指出。以免更多的人被误导。 |