是否可以使用内部查询的输出来输入外部查询的输入?

yhxst69z  于 2021-06-24  发布在  Hive
关注(0)|答案(1)|浏览(288)

就像

select ho from (select sourceaddress,count(sourceaddress) as 
src,hour(eventtime) as ho 
from default.fullandfinal 
where sourceaddress='0.0.0.0' and  eventtime between '2019-05-11 00:00:00' and 
'2019-05-11 19:59:59'  
group by sourceaddress,hour(eventtime) order by sourceaddress,ho) t where 
src=28350;

这个查询的输出是11,我想在我的nxt查询中使用这个输出

select sourceaddress,destinationaddress,destinationport,name,count(*) as count  
from fullandfinal 
where eventtime like "11%" and sourceaddress='0.0.0.0'  
group by sourceaddress,destinationaddress,destinationport,name 
order by count desc limit 5;

我想为这个写一个查询,可以吗?

9w11ddsr

9w11ddsr1#

考虑mysql-是的,可能。必须将第一个查询用作第二个查询中的子查询。查询结构如下-

SELECT *,
(SELECT ID FROM TABLE_1 WHERE ....) AS [From Other Query] -- In the selection part 
FROM TABLE_2
WHERE TABLE_2.ID = (SELECT ID FROM TABLE_1 WHERE ....) -- In Where condition

对于上述两种情况,您必须确保子查询返回一个值,该值与您提到的11类似。
试试这个-

SELECT sourceaddress,
destinationaddress,
destinationport,
[name],
COUNT(*) as [count]  
FROM fullandfinal 
WHERE 
eventtime LIKE
(
    SELECT ho FROM 
    (
        SELECT sourceaddress,
        COUNT(sourceaddress) AS src,
        HOUR(eventtime) AS ho 
        FROM DEFAULT.fullandfinal 
        WHERE sourceaddress='0.0.0.0' 
        AND  eventtime BETWEEN '2019-05-11 00:00:00' AND '2019-05-11 19:59:59'  
        GROUP BY sourceaddress,
        HOUR(eventtime) 
        ORDER BY sourceaddress,ho
    ) t
    WHERE src=28350
) + '%' 
AND sourceaddress='0.0.0.0'  
GROUP BY sourceaddress,destinationaddress,destinationport,name 
ORDER BY COUNT(*) DESC 
limit 5;

相关问题