查询SQL » 历史记录 » 版本 1
星 yan, 2026-10-09 11:17
| 1 | 1 | 星 yan | # 查询SQL |
|---|---|---|---|
| 2 | ``` sql |
||
| 3 | SELECT |
||
| 4 | t1.*, |
||
| 5 | sc.config_value AS guarantee_ratio, |
||
| 6 | ISNULL(z1.current_year_guarantee_count, 0) AS current_year_guarantee_count, |
||
| 7 | ISNULL(z1.current_year_share_count, 0) AS current_year_share_count, |
||
| 8 | ISNULL(z1.current_year_payment_count, 0) AS current_year_payment_count, |
||
| 9 | ISNULL(z1.next_year_guarantee_count, 0) AS next_year_guarantee_count, |
||
| 10 | ISNULL(z1.next_year_share_count, 0) AS next_year_share_count, |
||
| 11 | ISNULL(z1.next_year_payment_count, 0) AS next_year_payment_count, |
||
| 12 | t1.total_amount * sc.config_value / 100-t1.current_year_used_amount AS current_year_unused_amount, |
||
| 13 | t1.total_amount-t1.current_year_payment_used_amount AS current_year_payment_unused_amount, |
||
| 14 | t1.total_amount * sc.config_value / 100-t1.next_year_used_amount AS next_year_unused_amount, |
||
| 15 | t1.total_amount-t1.next_year_payment_used_amount AS next_year_payment_unused_amount |
||
| 16 | FROM hyx_guarantee_account t1 |
||
| 17 | LEFT JOIN sys_config sc ON sc.deleted = 0 AND sc.config_key = 'guarantee_ratio' |
||
| 18 | LEFT JOIN ( |
||
| 19 | SELECT |
||
| 20 | guarantee_account_id, |
||
| 21 | SUM(CASE WHEN limited_year=YEAR(GETDATE()) AND guarantee_method IN (1,4,5) THEN 1 ELSE 0 END) AS current_year_guarantee_count, |
||
| 22 | SUM(CASE WHEN limited_year=YEAR(GETDATE()) AND guarantee_method=3 THEN 1 ELSE 0 END) AS current_year_share_count, |
||
| 23 | SUM(CASE WHEN limited_year=YEAR(GETDATE()) AND guarantee_method=2 THEN 1 ELSE 0 END) AS current_year_payment_count, |
||
| 24 | 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, |
||
| 25 | SUM(CASE WHEN limited_year=(YEAR(GETDATE())+1) AND guarantee_method=3 THEN 1 ELSE 0 END) AS next_year_share_count, |
||
| 26 | SUM(CASE WHEN limited_year=(YEAR(GETDATE())+1) AND guarantee_method=2 THEN 1 ELSE 0 END) AS next_year_payment_count |
||
| 27 | FROM hyx_online_certificate |
||
| 28 | WHERE deleted=0 AND guarantee_account_id IS NOT NULL AND status IN(0,1) AND retreat_status !=1 |
||
| 29 | |||
| 30 | GROUP BY guarantee_account_id |
||
| 31 | ) z1 ON z1.guarantee_account_id = t1.id |
||
| 32 | WHERE t1.deleted = 0 |
||
| 33 | AND t1.account_no LIKE '%0080968%' |
||
| 34 | ORDER BY account_no ASC |
||
| 35 | |||
| 36 | ``` |