scala

beeline>create table testThrift (field1 String , field2 Int);

beeline>insert into table testThrift select c.theyear,max(d.sumofamount) from tbDate c join (select a.dateid,a.ordernumber,sum(b.amount) as sumofamount from tbStock a join tbStockDetail  b on a.ordernumber=b.ordernumber group by a.dateid,a.ordernumber ) d  on c.dateid=d.dateid group by c.theyear sort by c.theyear;

 

 

scala
scala>
import org.apache.spark.sql.SQLContext
var sqlContext = new SQLContext(sc)
import sqlContext.implicits._
var url = "jdbc:mysql://slave02:3306/testdb?useUnicode=true&characterEncoding=gbk&zeroDateTimeBehavior=convertToNull"
var table = "stu_score"
var reader = sqlContext.read.format("jdbc")
var reader = sqlContext.read.format("jdbc")
reader.option("url",url)
reader.option("dbtable",table)
reader.option("driver","com.mysql.jdbc.driver")
reader.option("user","feifyu")
reader.option("password","feiyu")
var DF = reader.load();
DF.show();

select c.theyear,max(d.sumofamount) from tbDate c join
(select a.dateid,a.ordernumber,sum(b.amount) as sumofamount from tbStock a join
tbStockDetail b
on a.ordernumber=b.ordernumber group by a.dateid,a.ordernumber ) d
on c.dateid=d.dateid group by c.theyear sort by c.theyear;