← 返回题库
初级

查询已绑定费用的账户列表

未完成
初级参考 完整示例代码供参考，建议自己理解后重新输入 
def solve():
    from pyodide.http import open_url
    offices_csv = open_url("https://data.zuihe.com/dbd/core/state_05/offices.csv").read()
    staff_csv = open_url("https://data.zuihe.com/dbd/core/state_05/staff.csv").read()
    savings_products_csv = open_url("https://data.zuihe.com/dbd/core/state_05/savings_products.csv").read()
    clients_csv = open_url("https://data.zuihe.com/dbd/core/state_05/clients.csv").read()
    savings_accounts_csv = open_url("https://data.zuihe.com/dbd/core/state_05/savings_accounts.csv").read()
    savings_account_transactions_csv = open_url("https://data.zuihe.com/dbd/core/state_05/savings_account_transactions.csv").read()
    gl_accounts_csv = open_url("https://data.zuihe.com/dbd/core/state_05/gl_accounts.csv").read()
    charges_csv = open_url("https://data.zuihe.com/dbd/core/state_05/charges.csv").read()
    savings_account_charges_csv = open_url("https://data.zuihe.com/dbd/core/state_05/savings_account_charges.csv").read()
    import sqlite3, pandas as pd
    from io import StringIO
    conn = sqlite3.connect(':memory:')
    pd.read_csv(StringIO(clients_csv)).to_sql('clients', conn, index=False, if_exists='replace')
    pd.read_csv(StringIO(savings_accounts_csv)).to_sql('savings_accounts', conn, index=False, if_exists='replace')
    pd.read_csv(StringIO(charges_csv)).to_sql('charges', conn, index=False, if_exists='replace')
    pd.read_csv(StringIO(savings_account_charges_csv)).to_sql('savings_account_charges', conn, index=False, if_exists='replace')
    result = pd.read_sql_query("""
        SELECT a.account_no, c.name AS client_name, ch.name AS charge_name, sac.status
        FROM savings_account_charges sac
        JOIN savings_accounts a ON sac.account_id=a.id
        JOIN clients c ON a.client_id=c.id
        JOIN charges ch ON sac.charge_id=ch.id
        ORDER BY a.account_no, ch.name
    """, conn)
    print(result.to_string(index=False))
    conn.close()

示例

输入
solve()
期望输出
account_no client_name charge_name      status
6212001000000001         张伟民    活期账户年管理费 OUTSTANDING
6212001000000001         张伟民       短信通知费        PAID
6212001000000002         李秀英    活期账户年管理费 OUTSTANDING
6212001000000002         李秀英       短信通知费        PAID
6212001000000005         赵志强       短信通知费        PAID
6212001000000008          周红       短信通知费        PAID
6212002000000003         王建国    活期账户年管理费 OUTSTANDING
6212002000000003         王建国       短信通知费        PAID
6212002000000015         张三丰       短信通知费        PAID
6212003000000010          郑艳       短信通知费        PAID
Python 代码 🔒 登录后使用
🔒

登录后即可练习

注册免费账号，在浏览器中直接运行 Python 代码

登录 免费注册