mysql行专列

本文主要是介绍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;

 

这篇关于mysql行专列的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---