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

最新推荐文章于 2025-07-13 15:43:32 发布

沧海远航

最新推荐文章于 2025-07-13 15:43:32 发布

阅读量548

点赞数 1

CC 4.0 BY-SA版权

分类专栏： SQL编写汇总

本文链接：https://blog.youkuaiyun.com/zyzyzyzyzy1989/article/details/79971800

SQL编写汇总专栏收录该内容

0 篇文章

订阅专栏

本文通过一个复杂的SQL查询实例，详细展示了如何从多个表中提取并整合数据，特别是使用嵌套查询来比较不同数据源之间的数量，从而实现精确的数据筛选与统计。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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