1 package com.xujunqi.zuoye
 2 
 3 import org.apache.flink.api.scala._
 4 import org.apache.flink.table.api.{Table, TableEnvironment}
 5 import org.apache.flink.types.Row
 6 
 7 object FlinkTable_Student {
 8   def main(args: Array[String]): Unit = {
 9     //配置运行环境
10     val env = ExecutionEnvironment.getExecutionEnvironment
11     //设置全局并行度
12     env.setParallelism(1)
13     //设置TableEnvironment
14     val tEnv = TableEnvironment.getTableEnvironment(env)
15     //学生数据
16     val stuData: List[(String, String, Int)] = List(
17       ("xiaoming", "english", 90),
18       ("xiaoming", "math", 80),
19       ("xiaohong", "english", 98),
20       ("xiaohong", "math", 82)
21     )
22     //读取数据
23     val stuDataSet: DataSet[(String, String, Int)] = env.fromCollection(stuData)
24     //封装数据
25     val stu: DataSet[Student] = stuDataSet.map(x => Student(x._1, x._2, x._3))
26     //注册表
27     tEnv.registerDataSet("stu", stu)
28 
29     //2.查询所有数据
30     val allData: Table = tEnv.sqlQuery("select * from stu")
31     //将Table转换成DataSet
32     val sql1 = tEnv.toDataSet[Row](allData)
33     println("查询所有数据：")
34     sql1.print()
35 
36     val sql2 = tEnv.toDataSet[Row](tEnv.sqlQuery("select subject,sum(score) from stu group by subject"))
37     println("统计学生各科总分：")
38     sql2.print()
39 
40     val sql3 = tEnv.toDataSet[Row](tEnv.sqlQuery("select name,avg(score) from stu group by name"))
41     println("统计学生平均分：")
42     sql3.print()
43 
44    val sql4 = tEnv.toDataSet[Row](tEnv.sqlQuery("select subject,max(score)  from  stu  group by subject"))
45     println("各科最高分的学生姓名：")
46     sql4.print()
47 
48     /*val sql5 = tEnv.toDataSet[Row](tEnv.sqlQuery("select s.* from (select subject,min(score) m from stu group by subject having subject='english') t,stu s where t.subject=s.subject and t.m=s.score"))
49     println("english最底分的学生姓名：")
50     sql5.print()
51 
52     val sql8 = tEnv.toDataSet[Row](tEnv.sqlQuery("select s.* from (select subject,max(score) m from stu group by subject having subject='english') t,stu s where t.subject=s.subject and t.m=s.score"))
53     println("english最高分的学生姓名：")
54     sql8.print()
55 */
56     val sql6 = tEnv.toDataSet[Row](tEnv.sqlQuery("select name,sum(score) from stu group by name"))
57     println("求出每名学生的总分：")
58     sql6.print()
59 
60     val sql7 = tEnv.toDataSet[Row](tEnv.sqlQuery("select name from stu group by name order by sum(score) desc limit 1"))
61     println("总分最高的学生姓名：")
62     sql7.print()
63 
64  /*   //统计学生人数
65     val sqlcount = tEnv.toDataSet[Row](tEnv.sqlQuery("select count(*)  from (select  count(*) from  stu group by  name "))
66     println("求学生人数")
67     sqlcount.print()*/
68   }
69 
70 }
71 
72 case class Student(name: String, subject: String, score: Int)
73 
74 /*
75 select t1.name from
76 (select max(t1.sumscore) as maxscore from
77 (select name, sum(score) as su) t2 join (select name, sum(score) as sumscore from t_stu group by name)
78 t1 on t2.maxscore=t1.sumscoremscore from t_stu group by name) t1*/
79 
80 /*
81 select t1.name from (select max(t1.sumscore) as maxscore from
82 (select name, sum(score) as sumscore from t_stu group by name) t1)
83 t2 join (select name, sum(score) as sumscore from t_stu group by name) t1 on t2.maxscore=t1.sumscore
84 * */