我有以下查询，但呈现结果似乎需要一分钟以上的时间。有人能帮我优化结果吗？我相信下面的第二个查询将有助于第一行，但不确定如何对进行分组
SELECT
(SELECT COUNT(delivery_id)
FROM DeliveryTons
WHERE EXISTS
(SELECT BSI_CODE
FROM FarmerGroups
WHERE ASSN = 'BSICGP'
AND BSI_CODE = DeliveryTons.farmer_pay_bsi
AND remarks = ''
AND DeliveryTons.crop_season = 3)) AS Tickets,
(SELECT COUNT(delivery_id)
FROM DeliveryTons
WHERE EXISTS
(SELECT BSI_CODE
FROM FarmerGroups
WHERE ASSN = 'BSICGP'
AND BSI_CODE = DeliveryTons.farmer_pay_bsi
AND DeliveryTons.crop_season = 3)) AS Deliveries,
(SELECT SUM(tonnage_adjusted)
FROM DeliveryTons
WHERE EXISTS
(SELECT BSI_CODE
FROM FarmerGroups
WHERE ASSN = 'BSICGP'
AND BSI_CODE = DeliveryTons.farmer_pay_bsi
AND delivery_status != 'C'
AND DeliveryTons.crop_season = 3)) AS TonsDelivered,
(SELECT SUM(tonnage_adjusted)
FROM DeliveryTons
WHERE EXISTS
(SELECT BSI_CODE
FROM FarmerGroups
WHERE ASSN = 'BSICGP'
AND BSI_CODE = DeliveryTons.farmer_pay_bsi
AND remarks = ''
AND DeliveryTons.crop_season = 3)) AS TonsMonitored,
(SELECT SUM(ACREAGE)
FROM CaneParcel
WHERE EXISTS
(SELECT DISTINCT(parcel_id)
FROM DeliveryTons
WHERE EXISTS
(SELECT BSI_CODE
FROM FarmerGroups
WHERE ASSN = 'BSICGP'
AND BSI_CODE = DeliveryTons.farmer_pay_bsi
AND CaneParcel.FIELD_ID = DeliveryTons.parcel_id
AND DeliveryTons.crop_season = 3))) AS AcresMonitored,
(SELECT SUM(ACREAGE)
FROM CaneParcel
WHERE EXISTS
(SELECT DISTINCT(parcel_id)
FROM DeliveryTons
WHERE EXISTS
(SELECT BSI_CODE
FROM FarmerGroups
WHERE ASSN = 'BSICGP'
AND BSI_CODE = DeliveryTons.farmer_pay_bsi
AND remarks = ''
AND CaneParcel.FIELD_ID = DeliveryTons.parcel_id
AND parcel_status = '3'
AND DeliveryTons.crop_season = 3))) AS ClosedAcres
SELECT COUNT(d.delivery_id),
FROM DeliveryTons d
INNER JOIN FarmerGroups F ON F.BSI_CODE = d.farmer_pay_bsi
WHERE t.assn = 'BSICGP'
AND crop_season = 3
AND remarks=''
GROUP BY d.delivery_id
这是我的DeliveryTons表：
id crop_season delivery_id parcel_id reaping_code farmer_owner_name farmer_owner_bsi farmer_pay_bsi tonnage_adjusted parcel_status remarks delivery_status
77701 1 1 038 - 0358 CGP001 Not defined 0 2064 15.3625 2 P
77702 1 2 038 - 0358 CGP001 Not defined 0 2064 17.4625 2 P
77703 1 3 038 - 0358 CGP001 Not defined 0 2064 13.5875 2 P
77704 1 4 038 - 0358 CGP001 Not defined 0 2064 19.0250 2 P
77705 1 5 038 - 0358 CGP001 Not defined 0 2064 19.6375 2 P
77706 1 6 038 - 0358 CGP001 Not defined 0 2064 16.2125 2 P
77707 1 7 038 - 0358 CGP001 Not defined 0 2064 22.5375 2 P
77708 1 8 038 - 0358 CGP001 Not defined 0 2064 18.1000 2 P
77709 1 9 038 - 0358 CGP001 Not defined 0 2064 23.0000 2 P
77710 1 11 016 - 0482 BSFA156 CANUL, MARTIN 844 7866 19.3000 1 P
77711 1 12 016 - 0096 BSFA153 ALDANA, MARCOS 986 10024 15.2625 1 P
77712 1 13 004 - 0684 BSFA155 QUEME, BENANCIO 778 10063 17.8125 1 P
77713 1 14 004 - 0224 BSFA162 CHABLE, MARCELINO 198 198 20.5125 1 P
77714 1 15 BSFA160 0 212 16.0375 0 NO_TICKET P
77715 1 16 BSFA159 0 4570 12.3875 0 NO_TICKET P
77716 1 17 016 - 2473 BSFA151 0 9173 13.6875 3 P
77717 1 18 016 - 1566 BSFA149 Not defined 0 1094 15.7250 1 P
77718 1 19 BSFA160 0 148 20.2125 0 NO_TICKET P
77719 1 20 016 - 1566 BSFA149 Not defined 0 1094 12.7625 2 P
77720 1 21 016 - 1566 BSFA149 Not defined 0 1094 15.3875 2 P
77721 1 22 016 - 2152 BSFA175 0 926 15.2625 3 P
77722 1 23 BSFA175 0 5498 13.7250 0 NO_TICKET P
甘蔗包裹表：
geoid FIELD_ID BSI_CODE ACREAGE ASSN
0 004 - 0567 47 7.359515 CSCPA
1 004 - 0008 281 12.38654 CSCPA
2 004 - 0012 281 7.899037 CSCPA
3 004 - 0013 281 4.937749 CSCPA
4 004 - 0014 281 5.002847 CSCPA
5 004 - 0038 9050 6.731369 CSCPA
6 004 - 0045 9028 4.120158 CSCPA
7 004 - 0039 9050 8.519387 CSCPA
8 004 - 0147 289 14.801 BSCFA - CZ
9 004 - 0572 47 5.878942 CSCPA
10 004 - 0030 281 14.33925 CSCPA
11 004 - 1150 281 7.306061 CSCPA
12 004 - 1152 281 3.814288 CSCPA
13 004 - 0017 281 11.5479 CSCPA
FarmerGroups表：
ID ASSN BSI_CODE FARMER CROP_SEASON
10473 BSCFA - OW 5752 GARCIA,EUGENIO R 2
10474 BSCFA - OW 8894 GARCIA,RAUL R 2
10475 BSCFA - OW 4427 PECH,EVELIA 2
10476 BSCFA - OW 5226 GONZALEZ,DALIA MARIA 2
10477 BSCFA - OW 5753 GONZALEZ,ELVIRA 2
10478 BSCFA - OW 9295 GONZALEZ,RAUL 2
10479 BSCFA - OW 9996 GONZALEZ,TRANSITO 2
10480 BSCFA - OW 10099 PERAZA,ALBERTA 2
10481 BSCFA - OW 4537 GARCIA,SANTIAGO SILVERIO 2
10482 BSCFA - OW 8573 ACK,AMELITA M 2
10483 BSCFA - OW 7467 ACK,EUCARIO 2
10484 BSCFA - OW 7468 ACK,MOISES 2
10485 BSCFA - OW 1848 AGUIRRE,NENCIE 2
10486 BSCFA - OW 4355 BLANCO,SILVIO 2
10487 BSCFA - OW 4476 CAL,ALBERTA GARCIA 2
10488 BSCFA - OW 4480 CAL,ENRIQUE 2
10489 BSCFA - OW 2545 CAL,ERNESTO T 2
10490 BSCFA - OW 6877 CAL,REMIGIO 2
在FarmerGroups可以为单个BSI_CODE记录创建多行的情况下，每个BSI_CODE一次农民组表的预聚合标志。然后你可以求和，而不会产生一个错误的笛卡尔结果。。。
将内部的"PQ"(PreQuery)想象成在行之间执行二进制OR。你所关心的只是任何一个BSI_CODE，它是否存在，它是否为空状态(或否)，以及它的交付状态。因此，给定的样本数据。。。
FarmerGroups
BSI_CODE Assn Remarks Delivery_Status
A BSICGP test B
A BSICGP C
A BSICGP test C
A BSICGP A
---------------------------------
B BSICGP test B
B BSICGP test C
---------------------------------
C BSICGP test B
---------------------------------
D BSICGP
D BSICGP X
---------------------------------
E BSICGP test1 C
E BSICGP test2 C
---------------------------------
现在，每个BSI_CODE的摘要将作为
BSI_CODE NoRemarks IsTonDelivery
A 1 1 (at least 1 record had no remarks, at least 1 record was NOT 'C' status)
B 0 1 (NO records had empty remarks, at least 1 was NOT 'C' status)
C 0 1 (only 1 record. It had a remark, but as NOT 'C' status)
D 1 1 (both no remarks, neither was 'C' status)
E 0 0 (both HAD remarks and BOTH were 'C' status).
所以你可以看到农民组的11条记录被总结成5行，分别的"flag"列为1或0。
在一种与您的"CaneParcel"有点类似的方法中，根据田地ID对面积进行预求和，这样每个田地也会有一行，以便与交付吨数相加。。。FarmerGroups的JOIN将通过传递条件预先限定该部分。
现在，如果您的DeliveryTons表有5条"D"记录，则求和、计数或乘以标志将导致0或试图获得的聚合值。你可以加入你的DeliveryTons表，由于你的每个子查询都依赖于Crop_Season=3，你可以将其应用于你的外部查询，但在。。。
select
count(*) as Deliveries,
sum( case when dt.remarks = '' then 1 else 0 end ) as Tickets,
sum( dt.Tonnage_Adjusted
* case when dt.delivery_status != 'C' then 1 else 0 end ) as TonsDelivered,
sum( dt.Tonnage_Adjusted
* case when dt.remarks = '' then 1 else 0 end ) as TonsMonitored,
sum( PQ2.totalAcreage ) AcresMonitored,
sum( case when dt.parcel_status = '3' then cp.Acreage else 0 end
* case when dt.remarks = '' then 1 else 0 end ) TotalClosedAcres
from
DeliveryTons dt
JOIN
(SELECT DISTINCT
BSI_CODE,
FROM
FarmerGroups fg
WHERE
ASSN = 'BSICGP' ) PQ
ON dt.farmer_pay_bsi = PQ.BSI_CODE
LEFT JOIN
(select
cp.Field_ID,
sum( cp.Acreage ) totalAcreage
from
CaneParcel cp
group by
cp.Field_ID ) PQ2
on dt.parcel_id = PQ2.Field_ID
where
dt.Crop_Season = 3
这可能有很多需要消化的地方，但请相信(在没有看到实际生产数据甚至样本数据的情况下)将作为两个快速预查询聚合(PQ和PQ2)一次并直接连接(左连接)到DeliveryTons表中。我会把它拆开试一下，看看/确认发生了什么。
根据评论更新
已更新查询。没有您的示例数据，我的查询是用隐含的格式/内容编写的。查看完您的数据后，请注意farmerGroups表中没有注释值，它在您的吨位表中也没有。已调整查询以适应。