楼主第二个不对呀，如果是mysql里面的话，应该是这样的： create temporary table T1 as select days as orderdate,product_id,tocnt,tocnt*price as totprice,(price-cost)*tocnt as totprofit from( select days,si.product_id as product_id,sum(cnt) as tocnt from sale_items si JOIN ( select order_id,substr(order_time,1,10) as days from sales where substr(order_time,1,7)='2019-06') s on si.order_id=s.order_id group by days,product_id order by days) temp JOIN product p on p.id=temp.product_id create temporary table T2 select * from T1; select T1.* from T1 left join T2 on T1.orderdate = T2.orderdate and T1.tocnt<T2.tocnt group by T1.orderdate,T1.orderdate,T1.tocnt,T1.totprice,T1.totprofit HAVING COUNT(T2.orderdate)<1 需要建立两个临时表，因为mysql里的临时表在同一个sql语句里面不能出现两次 如果是hive的话，就简单多了，分组取topN直接用窗口函数就行