在平时开发、学习、面试中，经常会遇到一些数据是需要根据数据生成字段的。就是我们常说的横向显示数据。
最近楼主运到了一个面试题，发现面试和实际工作的做法有点不同。
CREATE TABLE `tablea` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`course` varchar(20) DEFAULT NULL,
`grade` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;楼主将用两种解题思路来做这道题目(ps:一个是面试，一个是工作)
题目如下:
1.面试中
根据 name 排序, 在根据需求把横向数据定义死。(缺点：课程名称定义死了，不利于扩展)
select name ,
max(case course when '语文' then grade else 0 end) 语文,
max(case course when '数学' then grade else 0 end) 数学,
max(case course when '物理' then grade else 0 end) 物理
from tablea
group by name
2.实际工作中
我的思路是
第一步获取所有数据
第二步去重分别得到 横向的课程数据集合，和纵向的第一列学生名称的集合
第三步 遍历 学生名称集合
第四步 在学生名称集合里面遍历课程数据集合
然后根据 学生名称 和 课程名称 去所有数据集合 找到 成绩
最后关联到学生的上去
第五步 利用map 封装数据 放回给前端
最后面生成json数据是这样的
{
"code": 200,
"data": {
"subject": [
"语文",
"数学",
"物理"
],
"students": [
{
"grades": [
{
"course": "语文",
"grade": 81
},
{
"course": "数学",
"grade": 83
},
{
"course": "物理",
"grade": 100
}
],
"name": "张三"
},
{
"grades": [
{
"course": "语文",
"grade": 74
},
{
"course": "数学",
"grade": 84
},
{
"course": "物理",
"grade": 100
}
],
"name": "李四"
}
]
},
"msg": "success"
}最后把接口给前端调用渲染
我在数据中添加了一个英语课程和数据，就自动扩展了
话不多说，直接上代码
bean包下面
package com.itbbs.bean;
/**
* @author tjx
*
* @param <T>
*
* 公共返回类
*/
public class ComResponseBean<T> {
private String msg ;
private int code;
private T data;
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
public int getCode() {
return code;
}
public void setCode(int code) {
this.code = code;
}
public T getData() {
return data;
}
public void setData(T data) {
this.data = data;
}
}
pojo包下面
package com.itbbs.pojo;
/**
* @作者: tjx
* @描述: 成绩 (科目 对应 成绩)
* @创建时间: 创建于11:56 2018/9/26
**/
public class Grade {
public Grade(String course, Integer grade) {
this.course = course;
this.grade = grade;
}
/**
* 课程
*/
private String course;
/**
* 成绩
*/
private Integer grade;
public String getCourse() {
return course;
}
public void setCourse(String course) {
this.course = course;
}
public Integer getGrade() {
return grade;
}
public void setGrade(Integer grade) {
this.grade = grade;
}
}
package com.itbbs.pojo;
import java.util.List;
/**
* @作者: tjx
* @描述: 学生
* @创建时间: 创建于14:10 2018/9/26
**/
public class Student {
/**
* 学生名称
*/
private String name;
private List<Grade> grades;
public List<Grade> getGrades() {
return grades;
}
public void setGrades(List<Grade> grades) {
this.grades = grades;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
package com.itbbs.pojo;
import java.util.List;
/**
* @作者: tjx
* @描述: 学生成绩
* @创建时间: 创建于11:14 2018/9/26
**/
public class StudentGrade {
private int id;
/**
* 学生名称
*/
private String name;
/**
* 课程
*/
private String course;
/**
* 成绩
*/
private Integer grade;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getCourse() {
return course;
}
public void setCourse(String course) {
this.course = course;
}
public Integer getGrade() {
return grade;
}
public void setGrade(Integer grade) {
this.grade = grade;
}
}
dao包下面
package com.itbbs.dao;
import com.itbbs.pojo.StudentGrade;
import java.util.ArrayList;
import java.util.List;
/**
* @作者: tjx
* @描述: 模拟dao
* @创建时间: 创建于11:16 2018/9/26
**/
public class StudentGradeDAO {
public List<StudentGrade> selectAll(){
List<StudentGrade> list = new ArrayList<>();
StudentGrade studentGrade1 = new StudentGrade();
studentGrade1.setId(1);
studentGrade1.setName("张三");
studentGrade1.setCourse("语文");
studentGrade1.setGrade(81);
StudentGrade studentGrade2 = new StudentGrade();
studentGrade2.setId(2);
studentGrade2.setName("张三");
studentGrade2.setCourse("数学");
studentGrade2.setGrade(83);
StudentGrade studentGrade3 = new StudentGrade();
studentGrade3.setId(3);
studentGrade3.setName("张三");
studentGrade3.setCourse("物理");
studentGrade3.setGrade(93);
StudentGrade studentGrade4 = new StudentGrade();
studentGrade4.setId(4);
studentGrade4.setName("李四");
studentGrade4.setCourse("语文");
studentGrade4.setGrade(74);
StudentGrade studentGrade5 = new StudentGrade();
studentGrade5.setId(5);
studentGrade5.setName("李四");
studentGrade5.setCourse("数学");
studentGrade5.setGrade(84);
StudentGrade studentGrade6 = new StudentGrade();
studentGrade6.setId(6);
studentGrade6.setName("李四");
studentGrade6.setCourse("物理");
studentGrade6.setGrade(94);
//添加外语科目
StudentGrade studentGrade7 = new StudentGrade();
studentGrade7.setId(6);
studentGrade7.setName("张三");
studentGrade7.setCourse("物理");
studentGrade7.setGrade(100);
StudentGrade studentGrade8 = new StudentGrade();
studentGrade8.setId(6);
studentGrade8.setName("李四");
studentGrade8.setCourse("物理");
studentGrade8.setGrade(100);
list.add(studentGrade1);
list.add(studentGrade2);
list.add(studentGrade3);
list.add(studentGrade4);
list.add(studentGrade5);
list.add(studentGrade6);
list.add(studentGrade7);
list.add(studentGrade8);
return list;
}
}
service包下面
package com.itbbs.service;
import com.itbbs.bean.ComResponseBean;
import com.itbbs.dao.StudentGradeDAO;
import com.itbbs.pojo.Grade;
import com.itbbs.pojo.Student;
import com.itbbs.pojo.StudentGrade;
import com.itbbs.utils.ArrayListUtil;
import java.util.*;
import java.util.stream.Collectors;
/**
* @作者: tjx
* @描述: 成绩模块业务层
* @创建时间: 创建于14:27 2018/9/26
**/
public class StudentGradeService {
//此处模拟dao
StudentGradeDAO dao = new StudentGradeDAO();
public ComResponseBean gradeList(){
//查询所有数据
List<StudentGrade> data = dao.selectAll();
//使用steam 去重 获取所有的科目
List<StudentGrade> courses = data.stream()
.filter(ArrayListUtil.distinctByKey(p -> p.getCourse())) //去重
.collect(Collectors.toList());
//使用steam 分组 获取所有学生
List<StudentGrade> names = data.stream()
.filter(ArrayListUtil.distinctByKey(p -> p.getName())) //去重
.collect(Collectors.toList());
//结果集
List<Student> students = new ArrayList<>();
List<String> subject = new ArrayList<>();
courses.forEach(course->subject.add(course.getCourse()));
//根据学生成绩找到 对应的科目成绩
names.forEach(student->{
//获取学生名称
String name = student.getName();
List<Grade> grades = new ArrayList<>();
//遍历科目找到 改学生所有科目成绩
courses.forEach(course->{
//获取科目
String courseName = course.getCourse();
//根据 学生名称 和 学生科目 筛选出符合条件的数据
StudentGrade studentGrade = data.stream()
.filter(p -> p.getName().equals(name) && p.getCourse().equals(courseName)) //筛选条件
.sorted(Comparator.comparing(StudentGrade::getGrade).reversed()) //根据筛选出来的结果进行排序
.findFirst().orElse(null);//获取排序后的第一个（也就是最大的）
//找到符合条件的成绩
grades.add(new Grade(courseName,studentGrade.getGrade()));
});
//创建学生类
Student stu = new Student();
stu.setGrades(grades);
stu.setName(name);
students.add(stu);
});
Map result = new HashMap<>();
result.put("subject",subject);
result.put("students",students);
ComResponseBean bean = new ComResponseBean();
bean.setCode(200);
bean.setMsg("success");
bean.setData(result);
return bean;
}
}
utils包下
package com.itbbs.utils;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.function.Function;
import java.util.function.Predicate;
public class ArrayListUtil {
/**
* 去重复元素
* @param keyExtractor
* @param <T>
* @return
*/
public static <T> Predicate<T> distinctByKey(Function<? super T, Object> keyExtractor) {
Map<Object, Boolean> map = new ConcurrentHashMap<>();
return t -> map.putIfAbsent(keyExtractor.apply(t), Boolean.TRUE) == null;
}
}
html代码
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>学生成绩</title>
<link rel="stylesheet" href="https://cdn.bootcss.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://cdn.bootcss.com/jquery/2.1.1/jquery.min.js"></script>
<script src="https://cdn.bootcss.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
<table class="table table-striped">
<h1 style="text-align: center;">学生成绩</h1>
<thead id="thead">
</thead>
<tbody id="tbody">
</tbody>
</table>
<script type="text/javascript">
//此处模拟ajax请求数据
function ajax() {
return {"code":200,"data":{"subject":["语文","数学","物理","外语"],"students":[{"grades":[{"course":"语文","grade":81},{"course":"数学","grade":83},{"course":"物理","grade":93},{"course":"外语","grade":60}],"name":"张三"},{"grades":[{"course":"语文","grade":74},{"course":"数学","grade":84},{"course":"物理","grade":94},{"course":"外语","grade":70}],"name":"李四"}]},"msg":"success"}
}
//调用ajax获取数据
var data = ajax();
if(data.code == 200){
//渲染表头
var subject = data.data.subject;
var subjectSize = subject.length;
var thead = $("#thead");
var th = "<tr><td>学生姓名</td>"
for (var i=0;i<subjectSize;i++) {
th+="<td>" + subject[i]+"</td>";
}
th += "</tr>";
thead.html(th);
//渲染表身体
var students = data.data.students;
var studentSize = students.length;
var tbody = $("#tbody");
var tb = '';
for (var i=0;i<studentSize;i++) {
//获取名称
tb+="<tr><td>"+students[i].name+"</td>";
var grades = students[i].grades;
//获取报名项目
for (var j=0;j<subjectSize;j++) {
//获取课程
var course = subject[j];
//获取改名称下的参赛名
for (var k=0;k<grades.length;k++) {
if(course == grades[k].course){
tb += "<td>"+grades[k].grade+"</td>";
continue;
}
}
}
}
tbody.html(tb);
}else{
alert("加载失败")
}
</script>
</body>
</html>