1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
DROP
TABLE
IF EXISTS `score`;
CREATE
TABLE
`score` (
`id`
int
(11)
NOT
NULL
AUTO_INCREMENT,
`class`
varchar
(255)
DEFAULT
NULL
,
`score`
double
DEFAULT
NULL
,
`userid`
int
(11)
DEFAULT
NULL
,
PRIMARY
KEY
(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8
DEFAULT
CHARSET=latin1;
INSERT
INTO
`score`
VALUES
(
'1'
,
'math'
,
'90'
,
'1'
);
INSERT
INTO
`score`
VALUES
(
'2'
,
'english'
,
'90'
,
'1'
);
INSERT
INTO
`score`
VALUES
(
'3'
,
'computer'
,
'80'
,
'1'
);
INSERT
INTO
`score`
VALUES
(
'4'
,
'sports'
,
'90'
,
'1'
);
INSERT
INTO
`score`
VALUES
(
'5'
,
'math'
,
'80'
,
'2'
);
INSERT
INTO
`score`
VALUES
(
'6'
,
'english'
,
'85'
,
'2'
);
INSERT
INTO
`score`
VALUES
(
'7'
,
'computer'
,
'100'
,
'2'
);
|
查询语句
1
2
3
4
5
6
7
|
SET
@EE=
''
;
set
@str_tmp=
''
;
SELECT
@EE:=CONCAT(@EE,
'SUM(IF(class=\''
,class,
'\''
,
',score,0)) AS '
,class,
','
)
as
aa
into
@str_tmp
FROM
(
SELECT
DISTINCT
class
FROM
score) A
order
by
length(aa)
desc
limit 1;
SET
@QQ=CONCAT(
'SELECT ifnull(score.userid,\'total\'),'
,
LEFT
(@str_tmp,char_length(@str_tmp)-1),
' ,SUM(score) AS TOTAL FROM score GROUP BY userid WITH ROLLUP'
);
PREPARE
stmt
FROM
@QQ;
EXECUTE
stmt ;
deallocate
prepare
stmt;
|
原来的记录:
新查询出的结果:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
DROP
TABLE
IF EXISTS `score`;
CREATE
TABLE
`score` (
`id`
int
(11)
NOT
NULL
AUTO_INCREMENT,
`class`
varchar
(255)
DEFAULT
NULL
,
`score`
double
DEFAULT
NULL
,
`userid`
int
(11)
DEFAULT
NULL
,
PRIMARY
KEY
(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8
DEFAULT
CHARSET=latin1;
INSERT
INTO
`score`
VALUES
(
'1'
,
'math'
,
'90'
,
'1'
);
INSERT
INTO
`score`
VALUES
(
'2'
,
'english'
,
'90'
,
'1'
);
INSERT
INTO
`score`
VALUES
(
'3'
,
'computer'
,
'80'
,
'1'
);
INSERT
INTO
`score`
VALUES
(
'4'
,
'sports'
,
'90'
,
'1'
);
INSERT
INTO
`score`
VALUES
(
'5'
,
'math'
,
'80'
,
'2'
);
INSERT
INTO
`score`
VALUES
(
'6'
,
'english'
,
'85'
,
'2'
);
INSERT
INTO
`score`
VALUES
(
'7'
,
'computer'
,
'100'
,
'2'
);
|
查询语句
1
2
3
4
5
6
7
|
SET
@EE=
''
;
set
@str_tmp=
''
;
SELECT
@EE:=CONCAT(@EE,
'SUM(IF(class=\''
,class,
'\''
,
',score,0)) AS '
,class,
','
)
as
aa
into
@str_tmp
FROM
(
SELECT
DISTINCT
class
FROM
score) A
order
by
length(aa)
desc
limit 1;
SET
@QQ=CONCAT(
'SELECT ifnull(score.userid,\'total\'),'
,
LEFT
(@str_tmp,char_length(@str_tmp)-1),
' ,SUM(score) AS TOTAL FROM score GROUP BY userid WITH ROLLUP'
);
PREPARE
stmt
FROM
@QQ;
EXECUTE
stmt ;
deallocate
prepare
stmt;
|
原来的记录:
新查询出的结果: