create table table_name(
id int primary key,
col1 char(2),
col2 char(2),
col3 int
);
insert into table_name 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);
CREATE TABLE rollup(
orderid int NOT NULL,
orderdate date NOT NULL,
empid int NOT NULL,
custid varchar(10) NOT NULL,
qty int NOT NULL,
PRIMARY KEY(orderid,orderdate));
INSERT INTO rollup SELECT 1,'2010-01-02',3,'A',10;
INSERT INTO rollup SELECT 2,'2010-04-02',2,'B',20;
INSERT INTO rollup SELECT 3,'2010-05-02',1,'A',30;
INSERT INTO rollup SELECT 4,'2010-07-02',3,'D',40;
INSERT INTO rollup SELECT 5,'2011-01-02',4,'A',20;
INSERT INTO rollup SELECT 6,'2011-01-02',3,'B',30;
INSERT INTO rollup SELECT 7,'2011-01-02',1,'C',40;
INSERT INTO rollup SELECT 8,'2009-01-02',2,'A',10;
INSERT INTO rollup SELECT 9,'2009-01-02',3,'B',20;
SELECT IFNULL(year,'合计') AS year,
A.sum as sum,
IFNULL(A.custid ,'ALL') AS custid
FROM
(
SELECT YEAR(orderdate) year,
SUM(qty) sum ,custid
FROM rollup -- WHERE custid = 'A'
GROUP BY custid ,year WITH ROLLUP
)
A
-- SELECT * FROM
-- (
-- SELECT IFNULL(class_id,'总计') as classid,SUM(stu_score) as score FROM student GROUP BY class_id WITH ROLLUP
-- ) A
-- ORDER BY score
SELECT
ifnull(col1, 'total') AS col1,
sum(IF(col2 = 'B1', col3, 0)) AS B1,
sum(IF(col2 = 'B2', col3, 0)) AS B2,
sum(IF(col2 = 'B3', col3, 0)) AS B3,
sum(IF(col2 = 'B4', col3, 0)) AS B4,
SUM(col3) AS TOTAL
FROM
table_name
GROUP BY
col1 WITH ROLLUP;
SELECT
IFNULL(col1,'total') AS total,
SUM(IF(col2='B1',col3,0)) AS B1,
SUM(IF(col2='B2',col3,0)) AS B2,
SUM(IF(col2='B3',col3,0)) AS B3,
SUM(IF(col2='B4',col3,0)) AS B4,
SUM(IF(col2='total',col3,0)) AS total
FROM (
SELECT col1,IFNULL(col2,'total') AS col2,SUM(col3) AS col3
FROM table_name
GROUP BY col1,col2
WITH ROLLUP
HAVING col1 IS NOT NULL
) AS A
GROUP BY col1
WITH ROLLUP;