mysql行转列

案例一丶

 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;

View Code

 

 

案例二丶

 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

View Code

 

转载于:https://www.cnblogs.com/chenze-Index/p/9987118.html