I have tried to retrieve count of the records that are depened on different conditions.I got my expected output but It seems big to me.
我试图检索在不同条件下依赖的记录的数量。我得到了我预期的输出,但对我来说似乎很大。
select count(b.verificationdtl_gid) as received,
(
select count(b.verificationdtl_gid)
from avs_trn_tverification a
inner join avs_trn_tverificationdtl b on a.verification_gid =b.verification_gid
inner join ver_mst_tverifier c on a.verifier_gid = c.verifier_gid
where c.verifier_gid='VFI1601203046' and b.sap_flag<>'Y'
)as Normal,
(
select count(b.verificationdtl_gid)
from avs_trn_tverification a
inner join avs_trn_tverificationdtl b on a.verification_gid =b.verification_gid
inner join ver_mst_tverifier c on a.verifier_gid = c.verifier_gid
where c.verifier_gid='VFI1601203046' and b.verification_status='Reject'
) as reject,
(
select count(b.verificationdtl_gid)
from avs_trn_tverification a
inner join avs_trn_tverificationdtl b on a.verification_gid =b.verification_gid
inner join ver_mst_tverifier c on a.verifier_gid = c.verifier_gid
where c.verifier_gid='VFI1601203046' and b.verification_status='Decline'
)as decline
from avs_trn_tverification a
inner join avs_trn_tverificationdtl b on a.verification_gid =b.verification_gid
inner join ver_mst_tverifier c on a.verifier_gid = c.verifier_gid
where c.verifier_gid='VFI1601203046'
if the query executes it produces the following result
如果查询执行它会产生以下结果
received Normal reject decline
----------- ----------- ----------- -----------
33 24 0 2
1 个解决方案
#1
4
Use conditional aggregation instead:
改为使用条件聚合:
select
count(b.verificationdtl_gid) as received,
count(case when b.sap_flag <> 'Y' THEN b.verificationdtl_gid end) as Normal,
count(case when b.verification_status ='Reject' THEN b.verificationdtl_gid end) as reject,
count(case when b.verification_status ='Decline' THEN b.verificationdtl_gid end) as reject
from avs_trn_tverification a
inner join avs_trn_tverificationdtl b
on a.verification_gid = b.verification_gid
inner join ver_mst_tverifier c
on a.verifier_gid = c.verifier_gid
where
c.verifier_gid = 'VFI1601203046'
#1
4
Use conditional aggregation instead:
改为使用条件聚合:
select
count(b.verificationdtl_gid) as received,
count(case when b.sap_flag <> 'Y' THEN b.verificationdtl_gid end) as Normal,
count(case when b.verification_status ='Reject' THEN b.verificationdtl_gid end) as reject,
count(case when b.verification_status ='Decline' THEN b.verificationdtl_gid end) as reject
from avs_trn_tverification a
inner join avs_trn_tverificationdtl b
on a.verification_gid = b.verification_gid
inner join ver_mst_tverifier c
on a.verifier_gid = c.verifier_gid
where
c.verifier_gid = 'VFI1601203046'