两个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