表结构:CREATE TABLE `b_student_score` (
`id` varchar(32) NOT NULL,
`score` varchar(10) DEFAULT NULL,
`stu_no` varchar(10) DEFAULT NULL,
`date` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of b_student_score
-- ----------------------------
INSERT INTO `b_student_score` VALUES ('1', '70', '101', '2012-09-13');
INSERT INTO `b_student_score` VALUES ('2', '67', '102', '2012-09-13');
INSERT INTO `b_student_score` VALUES ('3', '82', '103', '2012-09-13');
INSERT INTO `b_student_score` VALUES ('4', '75', '101', '2012-10-25');
INSERT INTO `b_student_score` VALUES ('5', '64', '102', '2012-10-25');
INSERT INTO `b_student_score` VALUES ('6', '79', '103', '2012-10-25');
INSERT INTO `b_student_score` VALUES ('7', '78', '101', '2012-11-13');
INSERT INTO `b_student_score` VALUES ('8', '69', '102', '2012-11-13');
INSERT INTO `b_student_score` VALUES ('9', '80', '103', '2012-11-13');
如何一条sql语句查询出如下形式记录:
十分感谢!sql查询mysql行列转换
`id` varchar(32) NOT NULL,
`score` varchar(10) DEFAULT NULL,
`stu_no` varchar(10) DEFAULT NULL,
`date` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of b_student_score
-- ----------------------------
INSERT INTO `b_student_score` VALUES ('1', '70', '101', '2012-09-13');
INSERT INTO `b_student_score` VALUES ('2', '67', '102', '2012-09-13');
INSERT INTO `b_student_score` VALUES ('3', '82', '103', '2012-09-13');
INSERT INTO `b_student_score` VALUES ('4', '75', '101', '2012-10-25');
INSERT INTO `b_student_score` VALUES ('5', '64', '102', '2012-10-25');
INSERT INTO `b_student_score` VALUES ('6', '79', '103', '2012-10-25');
INSERT INTO `b_student_score` VALUES ('7', '78', '101', '2012-11-13');
INSERT INTO `b_student_score` VALUES ('8', '69', '102', '2012-11-13');
INSERT INTO `b_student_score` VALUES ('9', '80', '103', '2012-11-13');
如何一条sql语句查询出如下形式记录:
十分感谢!sql查询mysql行列转换
sum(if(stu_no=101,score,0)) as stu_no_101,
sum(if(stu_no=102,score,0)) as stu_no_102,
sum(if(stu_no=103,score,0)) as stu_no_103
from b_student_score
group by `date`
MySQL交叉表
在某些数据库中有交叉表,但在MySQL中却没有这个功能,但网上看到有不少朋友想找出一个解决方法,特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下:数据样本: create table tx( id int primary key, c1 c...
max(case stu_no when '102' then score else null end) as 'stu_no_102',
max(case stu_no when '103' then score else null end) as 'stu_no_103'
from b_student_score group by date order by date
select `date`,
sum(case when stu_no=101 then score else 0 end) as stu_no_101,
sum(case when stu_no=102 then score else 0 end) as stu_no_102,
sum(case when stu_no=103 then score else 0 end) as stu_no_103
from b_student_score
group by `date`动态的用字符串累计生成SQL语句的方法
select date,
sum(if(stu_no=101,score,0)) as stu_no_101,
sum(if(stu_no=102,score,0)) as stu_no_102,
sum(if(stu_no=103,score,0)) as stu_no_103
from b_student_score group by date
就可以了
静态:
select date,
sum(if(stu_no=101,score,0)) as stu_no_101,
sum(if(stu_no=102,score,0)) as stu_no_102,
sum(if(stu_no=103,score,0)) as stu_no_103
from b_student_score
group by date;