mysql动态行转列
来源:互联网 发布:阿里云部署html网页 编辑:程序博客网 时间:2024/06/07 23:20
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;
原来的记录:
新查询出的结果:
0 0
- MySQL动态行转列
- MySql 动态行转列整理
- MySql动态行转列
- MySQL 动态行转列
- mysql动态行转列
- mysql动态行转列
- mysql 动态行转列
- mysql 动态行转列
- MYSQL动态行转列
- mysql 动态行转列
- mysql 动态行转列
- 数据库--mysql中动态行转列
- MySQL存储过程的动态行转列
- MySQL存储过程中使用动态行转列
- MySQL存储过程的动态行转列
- MySQL存储过程中使用动态行转列
- MySQL存储过程中使用动态行转列
- mysql动态sql语句
- linux中wait系统调用1
- php intval()函数
- JAVA RMI远程方法调用简单实例
- windows平台上多网卡路由配置方法IP在dos下的操作大全
- 我的观念
- mysql动态行转列
- 【iOS开发】tableView自动移动到底部
- Android绘制弧形进度条
- Xcode 7 缺少 *.dylib库的解决方法
- git 学习笔记2
- 二分的初步= =
- P6Spy
- 关于软件工程的一些基本概念
- n流水线m装配站调度问题