Heim >Backend-Entwicklung >PHP-Tutorial >如何做数据库的行列转置
thinkphp和mysql。数据库如图所示:
建表的sql语句如下:
CREATE TABLE `score_collect` ( `std_id` int(10) NOT NULL, `course_id` int(11) NOT NULL, `score` varchar(255) DEFAULT NULL, PRIMARY KEY (`std_id`,`course_id`), )
INSERT INTO `score_collect` VALUES ('2012508082', '2', '20'); INSERT INTO `score_collect` VALUES ('2012508082', '3', '10'); INSERT INTO `score_collect` VALUES ('2012508082', '4', '30'); INSERT INTO `score_collect` VALUES ('2012508084', '2', '21'); INSERT INTO `score_collect` VALUES ('2012508084', '3', '11'); INSERT INTO `score_collect` VALUES ('2012508084', '4', '31');
SELECT std_id,GROUP_CONCAT(course_id) AS course_id,GROUP_CONCAT(score) AS score FROM `score_collect` GROUP BY std_id;
//$list=array(array('std_id'=>'2012508082','course_id'=>'2,3,4','score'=>'20,10,30'),array('std_id'=>'2012508084','course_id'=>'2,3,4','score'=>'21,11,31'),);foreach($list as $key=>$val){ $course_id_arr=explode(',',$val['course_id']); $score_arr=explode(',',$val['score']); unset($list[$key]['course_id']); unset($list[$key]['score']); foreach($course_id_arr as $key1=>$val1) { $list[$key][$val1]=$score_arr[$key1]; }}print_r($list);