mysql 行专列 oracle 行专列 数据库 行专列

mysql 行专列 oracle 行专列  数据库 行专列

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 行专列 oracle 行专列  数据库 行专列

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>