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:一個是面試,一個是工作)
題目如下:
解題思路
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>複製程式碼