MYSQL列转行的两种做法
DROP TABLE IF EXISTS `sys_student`;
CREATE TABLE `sys_student` (
`id` bigint(20) NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`subject` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`score` int(11) NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of sys_student
-- ----------------------------
INSERT INTO `sys_student` VALUES (1, '小明', '语文', 96);
INSERT INTO `sys_student` VALUES (2, '小明', '数学', 67);
INSERT INTO `sys_student` VALUES (3, '小明', '英语', 89);
INSERT INTO `sys_student` VALUES (4, '大花', '语文', 99);
INSERT INTO `sys_student` VALUES (5, '大花', '语文', 94);
1、sql法
SELECT NAME AS
'姓名',
MAX( CASE WHEN SUBJECT = '语文' THEN score ELSE 0 END ) AS '语文',
MAX( CASE WHEN SUBJECT = '数学' THEN score ELSE 0 END ) AS '数学',
MAX( CASE WHEN SUBJECT = '英语' THEN score ELSE 0 END ) AS '英语'
FROM
sys_student
GROUP BY
NAME
如果是oracle或者pgsql，则可以
SELECT *
FROM student
PIVOT (
SUM(score) FOR subject IN (语文, 数学, 英语)
)
2、反射法
/**
* 通过反射获取字段的值
* @param fieldName 字段名
* @param t 对象
* @param <T> 对象类型
* @return
*/
public <T> Object getValueByReflect(String fieldName, T t) {
String methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
try {
Method method = t.getClass().getMethod(methodName);
Object fieldValue = method.invoke(t);
return fieldValue;
} catch (Exception e) {
logger.error("通过反射获取字段值失败:{}", e);
return null;
}
}
通过这两种方法皆可把行数据转为对象返回
