sql之多查询结果合并
sql之多查询结果合并
根据需求查询，出现多个结果集,⽽且没有交集，但数据类型⼀样，进⾏合并;
语法:
sql中union多表合并1. UNION：去除重复记录，例如：SELECT * FROM t1 UNION SELECT * FROM t2；
2. UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2
代码:
val conf = new SparkConf().setAppName("").setMaster("local")
val spark = SparkSession.builder().config(conf).getOrCreate()
import spark.implicits._
val rdd = File("f:/ab/").map(_.split("[ \t]+")).map(x => (x(0), x(1).toInt))
var df = DF("a1", "a2")
//  println("")
spark.sql("select a1,count(a1) c1 from stu group by a1").show
println("统计出每个⽤户的粉丝数量，按照粉丝数量排序并输出")
spark.sql("select a2,count(a2) c2 from stu group by a2 order by c2 desc").show
//  SELECT * FROM t1 UNION SELECT * FROM t2
println("请统计出⼀共有多少个不同的ID  ok")
spark.sql("select count(a1) from (select a1,count(a1) c1 from stu group by a1    union        select a2,count(a2) c2 from stu group by a2 order by c1 desc) z ong").show
println("统计出每个ID出现的次数")
spark.sql("select a1,count(a1) c1 from stu group by a1    union        select a2,count(a2) c2 from stu group by a2 order by c1 desc").show
运⾏结果:
+--------+---+
|      a1| c1|
+--------+---+
|11111111|  9|
|11111113|  7|
|11111112|  7|
+--------+---+
统计出每个⽤户的粉丝数量，按照粉丝数量排序并输出+--------+---+
|      a2| c2|
+--------+---+
|12743457|  3|
|13256236|  3|
|10000001|  3|
|12356363|  2|
|34232443|  2|
|10000032|  2|
|16386587|  2|
|13426275|  2|
|19764388|  1|
|12357365|  1|
|12364375|  1|
|32435546|  1|
+--------+---+
请统计出⼀共有多少个不同的ID  ok
+---------+
|count(a1)|
+---------+
|      15|
+---------+
统计出每个ID出现的次数
+--------+---+
|      a1| c1|
+--------+---+
|11111111|  9|
|11111113|  7|
|11111112|  7|
|10000001|  3|
|12743457|  3|
|13256236|  3|
|13426275|  2|
|10000032|  2|
|34232443|  2|
|12356363|  2|
|16386587|  2|
|32435546|  1|
|12364375|  1|
|19764388|  1|
|12357365|  1|
+--------+---+

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。