mysql行專列

mysql group語句和if語句結合運用！

 

//建表

CREATE TABLE `tx` (
  `id` int(11) NOT NULL default '0',
  `c1` varchar(255) character set utf8 default NULL,
  `c2` varchar(255) character set utf8 default NULL,
  `c3` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

//填入資料
insert into tx values
(1 ,'A1','B1',9),
(2 ,'A2','B1',7),
(3 ,'A3','B1',4),
(4 ,'A4','B1',2),
(5 ,'A1','B2',2),
(6 ,'A2','B2',9),
(7 ,'A3','B2',8),
(8 ,'A4','B2',5),
(9 ,'A1','B3',1),
(10 ,'A2','B3',8),
(11 ,'A3','B3',8),
(12 ,'A4','B3',6),
(13 ,'A1','B4',8),
(14 ,'A2','B4',2),
(15 ,'A3','B4',6),
(16 ,'A4','B4',9),
(17 ,'A1','B4',3),
(18 ,'A2','B4',5),
(19 ,'A3','B4',2),
(20 ,'A4','B4',5);

 

//sql語句1
SELECT
     c1,
     SUM(IF(c2='B1',c3,0)) AS B1,
     SUM(IF(c2='B2',c3,0)) AS B2,
     SUM(IF(c2='B3',c3,0)) AS B3,
     SUM(IF(c2='B4',c3,0)) AS B4
 FROM tx
 GROUP BY c1

 

//sql語句2

SELECT
     IFNULL(c1,'total') AS total,
     SUM(IF(c2='B1',c3,0)) AS B1,
     SUM(IF(c2='B2',c3,0)) AS B2,
     SUM(IF(c2='B3',c3,0)) AS B3,
     SUM(IF(c2='B4',c3,0)) AS B4
 FROM tx
 GROUP BY c1
 WITH ROLLUP;

 

//sql語句3

SELECT
   IFNULL(c1,'total') AS total,
   SUM(IF(c2='B1',c3,0)) AS B1,
     SUM(IF(c2='B2',c3,0)) AS B2,
     SUM(IF(c2='B3',c3,0)) AS B3,
     SUM(IF(c2='B4',c3,0)) AS B4,
     SUM(IF(c2='total',c3,0)) AS total
 FROM (
   SELECT c1,IFNULL(c2,'total') AS c2,SUM(c3) AS c3
     FROM tx
    GROUP BY c1,c2
     WITH ROLLUP
    HAVING c1 IS NOT NULL
) AS A
 GROUP BY c1
 WITH ROLLUP;