查询SQL¶

SELECT 
t1.*,
sc.config_value AS guarantee_ratio,
ISNULL(z1.current_year_guarantee_count, 0) AS current_year_guarantee_count,
ISNULL(z1.current_year_share_count, 0) AS current_year_share_count,
ISNULL(z1.current_year_payment_count, 0) AS current_year_payment_count,
ISNULL(z1.next_year_guarantee_count, 0) AS next_year_guarantee_count,
ISNULL(z1.next_year_share_count, 0) AS next_year_share_count,
ISNULL(z1.next_year_payment_count, 0) AS next_year_payment_count,
t1.total_amount * sc.config_value / 100-t1.current_year_used_amount AS current_year_unused_amount,
t1.total_amount-t1.current_year_payment_used_amount AS current_year_payment_unused_amount,
t1.total_amount * sc.config_value / 100-t1.next_year_used_amount AS next_year_unused_amount,
t1.total_amount-t1.next_year_payment_used_amount AS next_year_payment_unused_amount
FROM hyx_guarantee_account t1
LEFT JOIN sys_config sc ON sc.deleted = 0 AND sc.config_key = 'guarantee_ratio'
LEFT JOIN (
    SELECT 
        guarantee_account_id,
        SUM(CASE WHEN limited_year=YEAR(GETDATE()) AND guarantee_method IN (1,4,5) THEN 1 ELSE 0 END) AS current_year_guarantee_count,
        SUM(CASE WHEN limited_year=YEAR(GETDATE()) AND guarantee_method=3 THEN 1 ELSE 0 END) AS current_year_share_count,
        SUM(CASE WHEN limited_year=YEAR(GETDATE()) AND guarantee_method=2 THEN 1 ELSE 0 END) AS current_year_payment_count,
        SUM(CASE WHEN limited_year=(YEAR(GETDATE())+1) AND guarantee_method IN (1,4,5) THEN 1 ELSE 0 END) AS next_year_guarantee_count,
        SUM(CASE WHEN limited_year=(YEAR(GETDATE())+1) AND guarantee_method=3 THEN 1 ELSE 0 END) AS next_year_share_count,
        SUM(CASE WHEN limited_year=(YEAR(GETDATE())+1) AND guarantee_method=2 THEN 1 ELSE 0 END) AS next_year_payment_count
    FROM hyx_online_certificate
WHERE deleted=0 AND guarantee_account_id IS NOT NULL AND status IN(0,1) AND retreat_status !=1

    GROUP BY guarantee_account_id
) z1 ON z1.guarantee_account_id = t1.id
WHERE t1.deleted = 0
AND t1.account_no LIKE '%0080968%'
ORDER BY account_no ASC