mysql行专列

来源：互联网发布：御宅同萌邀请码淘宝编辑：程序博客网时间：2024/04/27 16:47

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;