背景介绍:
1. 需要按照车辆类型分类,找出省油冠军(百公里油耗最低);
普通分组函数可以找出对应的车辆类型及省油冠军,但是无法查询到车辆唯一标识;
select MIN(TO_NUMBER(M.OIL100MILE)),B.C_USE_TYPE from iov_screen_data_month M
left join ANALYSIS.IOV_CAR_BASIC_INFO b on M.TERMINAL_ID = b.C_TID
WHERE M.MILEAGE > 10000 AND M.OIL_COST > 3000 AND B.C_USE_TYPE IS NOT NULL
GROUP BY B.C_USE_TYPE;
group by后添加vin码,会查询出所有车的信息,不符合需求;
方案一:
借助 WITH AS 查询语句进行查询;
WITH TEMP AS (SELECT B.C_VIN_8 VIN8, S.TOTAL_MILEAGE COUNT, B.C_USE_TYPE TP FROM IOV_SCREEN_RECORD_UPDATE S LEFT JOIN
ANALYSIS.IOV_CAR_BASIC_INFO B ON S.TERMINAL_ID = B.C_TID)
SELECT * FROM TEMP WHERE TEMP.COUNT IN (SELECT MAX(TO_NUMBER(TEMP.COUNT)) FROM TEMP