两个groupby查询出来的结果作为where条件比较大小，不嵌套有难度

1、通过笛卡尔积把这两个结果都查出来

2、把上次查询出来的结果作为作为内嵌表，利用内层表查出来的结果比较大写

特别注意：红字部分直接放在嵌套子查询语句的外部，不然考虑不周

 select count(CAR_STATE_BILL.PK_CAR_STATE_BILL) from (select CAR_STATE_BILL.PK_CAR_STATE_BILL,

       CAR_STATE_BILL.SE_CS_CODE,
       CAR_STATE_BILL.SE_CS_NAME,
       CAR_STATE_BILL.SE_STARTIME,
       CAR_STATE_BILL.SE_ENDTIME,
       CAR_STATE_BILL.CONTRACT_CODE,
       CAR_STATE_BILL.CONTRACT_NAME,
       CAR_STATE_BILL.CONTRACT_TYPE,
       CAR_STATE_BILL.PK_CONTRACT,
       CAR_STATE_BILL.CHEC_COMPANY_NAME,
       CAR_STATE_BILL.CHEC_COMPANY,
       CAR_STATE_BILL.PK_PRDAILY_CONTRACT,
       CAR_STATE_BILL.PRDAILY_CONTRACT_NAME,
       CAR_STATE_BILL.PRDAILY_CONTRACT_CODE,
       CAR_STATE_BILL.CUSCODE,
       CAR_STATE_BILL.CUS_NAME,
       CAR_STATE_BILL.SUP_CODE,
       CAR_STATE_BILL.SUP_NAME,
       CAR_STATE_BILL.PROJECTDEPT_NAME,
       CAR_STATE_BILL.PK_PROJECTDEPT,
       CAR_STATE_BILL.PK_PROJECTINFO,
       CAR_STATE_BILL.PROJECT_NAME,
       CAR_STATE_BILL.SE_NUM,
       CAR_STATE_BILL.SE_AMOUNT,
       CAR_STATE_BILL.PID,
       CAR_STATE_BILL.BILLTYPE,
       CAR_STATE_BILL.STATUS,
       CAR_STATE_BILL.APPR_RESULT,
       CAR_STATE_BILL.REMARK,
       CAR_STATE_BILL.PK_CREATOR,
       CAR_STATE_BILL.CREATOR_NAME,
       CAR_STATE_BILL.CREATOR_TIME,
       CAR_STATE_BILL.MODIFIER,
       CAR_STATE_BILL.MODIFIERNAME,
       CAR_STATE_BILL.MODIFIEDTIME,
       CAR_STATE_BILL.FREE1,
       CAR_STATE_BILL.FREE2,
       CAR_STATE_BILL.FREE3,
       CAR_STATE_BILL.FREE4,
       CAR_STATE_BILL.FREE5,
       CAR_STATE_BILL.DR,
       CAR_STATE_BILL.CAR_MATCLASSES,
       CAR_STATE_BILL.CAR_MATCLASSES_NAME,
       CAR_STATE_BILL.COUNT_CURR,
       CAR_STATE_BILL.COMPRICE_CURR,
       (select SUM(COUNT) as a from car_state_billdetail where car_state_billdetail.pk_car_state_bill=  CAR_STATE_BILL.PK_CAR_STATE_BILL group by car_state_billdetail.pk_car_state_bill) as a,
       NVL((SELECT SUM(COUNT)
                                         FROM CAR_INSTOR_BILL_DETAIL
                                        WHERE CAR_INSTOR_BILL_DETAIL.PK_CAR_STATE_BILLDETAIL =
                                              CAR_STATE_BILL.PK_CAR_STATE_BILL
                                        group by CAR_INSTOR_BILL_DETAIL.PK_CAR_STATE_BILLDETAIL),
                                       0)as b
  from CAR_STATE_BILL
 WHERE 1 = 1
   AND CAR_STATE_BILL.STATUS = '2'
   and CAR_STATE_BILL.PK_PROJECTINFO = '2021219200000000014'
   and pk_projectinfo like '%2021219200000000014%'
   and (CAR_STATE_BILL.PROJECT_NAME in
       (select project_name
           from project_info
          where org_partyid in
                (select distinct t.partyid
                   from au_partyrelation t
                  START WITH t.partyid in
                             ('2021100700000000004',
                              '2021100700000000007',
                              '2017100700000000032')
                 CONNECT BY PRIOR t.partyid = t.parent_partyid)))  
)  CAR_STATE_BILL where CAR_STATE_BILL.a>CAR_STATE_BILL.b
 ORDER BY CAR_STATE_BILL.STATUS DESC, CAR_STATE_BILL.CREATOR_TIME DESC