sql之多查询结果合并

sql之多查询结果合并

根据需求查询,出现多个结果集,而且没有交集,但数据类型一样,进行合并;

语法:

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 = spark.read.textFile("f:/ab/input/c.txt").map(_.split("[ \t]+")).map(x => (x(0), x(1).toInt))
var df = rdd.toDF("a1", "a2")
df.createOrReplaceTempView("stu")
//  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) zong").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|
+--------+---+