子查询一问

Question

select 
  a.serial_station_status 
from 
  station_realtime a,
  (select customer_id,max(station_scaned_index) from idcora.station_realtime group by customer_id) b
  WHERE a.customer_id=b.customer_id;

Answer 1

select serial_station_status from 
(select t.*,rank() over(partition by customer_id order by station_scaned_index desc) rk
from station_realtime t) tt
where rk=1;

Answer 2

or:
select serial_station_status from station_realtime 
WHERE (customer_id,station_scaned_index) IN
 (select customer_id,max(station_scaned_index) from idcora.station_realtime group by customer_id);

Answer 3

select 
  a.serial_station_status 
from 
  station_realtime a,
  (select customer_id,max(station_scaned_index) station_scaned_index 
    from idcora.station_realtime group by customer_id) b
  WHERE a.customer_id=b.customer_id and and
        a.station_scaned_index=b.station_scaned_index;

Answer 4

如果想让customer_id作为父查询的条件的话，四楼方法可以。无非就是按某列分组后取每组中这个列最大值的记录。
顶 bzszp的