应用场景:
我们在A表中有需要的3个字段,B表中有需要的2个字段,如何把两个表显示到一张表中呢,这时候我们会用到多表连接查询。
第一步:
组建A表
SELECT Clientmachine_ID,Target_port,COUNT(1) AS 次数
FROM IP222入站请求数据收集20200329
GROUP BY Clientmachine_ID,Target_port
HAVING Target_port <> 80;
表A查询结果为:
第二步:
创建B表
SELECT CLIENTMACHINE_ID,TARGET_PORT ,SUBSTR(Record_time,0,7) as 登陆月份 ,COUNT(1) AS 月登陆次数
FROM IP222入站请求数据收集20200329
GROUP BY CLIENTMACHINE_ID,TARGET_PORT,SUBSTR(Record_time,0,7)
HAVING TARGET_PORT <> 80;
表B查询结果为:
第三步:
将两个表并列到一起在这里我们使用LEFT (OUTER) JOIN ,左外连接查询结果集中不仅包含匹配的行还包括左表中所有满足WHERE 限制的行,而不论是否与右表相匹配。(详见《Oracle从入门到精通》第二版第61页)
基本语句为:
select * from A left join B on A.id = B.id
联合查询后的 代码为:
SELECT a.Clientmachine_ID,a.Target_port,a.次数,b.登陆月份,b.月登陆次数 FROM (select Clientmachine_ID,Target_port,COUNT(1) AS 次数 FROM IP222入站请求数据收集20200329 GROUP BY Clientmachine_ID,Target_port HAVING Target_port <> 80 ) A
LEFT JOIN
(SELECT CLIENTMACHINE_ID,TARGET_PORT ,SUBSTR(Record_time,0,7) as 登陆月份 ,COUNT(1) AS 月登陆次数 FROM IP222入站请求数据收集20200329 GROUP BY CLIENTMACHINE_ID,TARGET_PORT,SUBSTR(Record_time,0,7) HAVING TARGET_PORT <> 80) B
ON a.Clientmachine_ID=b. Clientmachine_ID AND a.Target_port=b.Target_port
ORDER BY a.次数 DESC ,a.Clientmachine_ID,b.月登陆次数 DESC;
运行结果如下: