转载

Java实现mysql横向数据面试题

在平时开发、学习、面试中,经常会遇到一些数据是需要根据数据生成字段的。就是我们常说的横向显示数据。

最近楼主运到了一个面试题,发现面试和实际工作的做法有点不同。

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:一个是面试,一个是工作)

题目如下:

Java实现mysql横向数据面试题

解题思路

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.实际工作中

我的思路是

第一步获取所有数据

Java实现mysql横向数据面试题

第二步去重分别得到 横向的课程数据集合,和纵向的第一列学生名称的集合

Java实现mysql横向数据面试题

第三步 遍历 学生名称集合

Java实现mysql横向数据面试题

第四步 在学生名称集合里面遍历课程数据集合

然后根据 学生名称课程名称 去所有 数据集合 找到 成绩

最后关联到学生的上去

Java实现mysql横向数据面试题

第五步 利用map 封装数据 放回给前端

Java实现mysql横向数据面试题

最后面生成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"
}复制代码

最后把接口给前端调用渲染

Java实现mysql横向数据面试题

我在数据中添加了一个英语课程和数据,就自动扩展了

Java实现mysql横向数据面试题

话不多说,直接上代码

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>复制代码
原文  https://juejin.im/post/5bab42c15188255c953834b1
正文到此结束
Loading...