LISTAGG(BS,',') WITHIN GROUP(ORDER BY ENGINE_NO)
SQL实例:
query.append("SELECT LICENSE_PLATE,SVIN,MAINTENANCE_DATE,DEFAULT_VIN,ENGINE_NO, "); query.append(" case when LISTAGG(BS,',') WITHIN GROUP(ORDER BY ENGINE_NO) = 'DRIVER,OWNER' then 'OWNER' "); query.append(" when LISTAGG(BS,',') WITHIN GROUP(ORDER BY ENGINE_NO) = 'OWNER' then 'OWNER' "); query.append(" when LISTAGG(BS,',') WITHIN GROUP(ORDER BY ENGINE_NO) = 'DRIVER'then 'DRIVER' "); query.append(" END BS "); query.append("FROM ( "); query.append(" SELECT DISTINCT * FROM (SELECT MV.LICENSE_PLATE,MV.SVIN, "); query.append(" TO_CHAR(MV.MAINTENANCE_DATE,'yyyy-MM-dd') MAINTENANCE_DATE,MVM.DEFAULT_VIN, "); query.append(" MV.ENGINE_NO,'OWNER' BS ");//OWNER 车主 query.append(" FROM CMS.MAIN_VEHICLE_MENBER MVM,MAIN_VEHICLE MV "); query.append(" WHERE MVM.OWNER_ID = MV.OWNER_ID "); query.append(" AND MVM.MOBILE = '"+telephone+"' "); query.append(" UNION "); query.append(" SELECT MV.LICENSE_PLATE,MV.SVIN,TO_CHAR(MV.MAINTENANCE_DATE,'yyyy-MM-dd') MAINTENANCE_DATE, ");//DRIVER 司机 query.append(" DV.DEFAULT_VIN,MV.ENGINE_NO, 'DRIVER' BS FROM ( "); query.append(" SELECT MVM.OWNER_ID,MVM.DEFAULT_VIN,MODR.VIN FROM CMS.MAIN_VEHICLE_MENBER MVM,CMS.MAIN_VEHICLE_DRIVER_RELATION MODR "); query.append(" WHERE MVM.OWNER_ID = MODR.DRIVER_ID "); query.append(" AND MVM.MOBILE = '"+telephone+"' "); query.append(" ) DV, MAIN_VEHICLE MV "); query.append(" WHERE DV.VIN = MV.SVIN) "); query.append(" ) GROUP BY LICENSE_PLATE,SVIN,MAINTENANCE_DATE,DEFAULT_VIN,ENGINE_NO ");