In the following text, change it to use advanced vocabulary but do not overuse it. Make sure to use proper grammar and spell check thoroughly. Show expertise in the subject provided, but do not add any extra information. Try to keep your response at the same length of words as the original. Use the same language as the original text. ONLY return the modified text and nothing else.



select tt.type
     , tt.student_id
     , tt.student_code
     , tt.student_name
     , tt.parent_identity
     , (select ddd.dict_value
        from data_dict_data ddd
        where ddd.dict_code = 'parentIdentity'
          and ddd.dict_data_code = tt.student_status
        limit 1)                                                            as parent_identity_name
     , tt.school_code
     , tt.school_name
     , tt.year_start
     , tt.term_code
     , tt.year_term_code
     , tt.year_term_name
     , tt.student_status
     , (select ddd.dict_value
        from data_dict_data ddd
        where ddd.dict_code = 'studentStatus'
          and ddd.dict_data_code = tt.student_status
        limit 1)                                                            as student_status_name
     , tt.phase_code
     , (select dsd.node_name
        from data_school_department dsd
        where dsd.deleted_status = 0
          and dsd.node_type = 1
          and dsd.school_code = tt.school_code
          and dsd.phase_code = tt.phase_code
        limit 1)                                                            as phase_name
     , tt.course_code
     , (select dsd.node_name
        from data_school_department dsd
        where dsd.deleted_status = 0
          and dsd.node_type = 2
          and dsd.school_code = tt.school_code
          and dsd.phase_code = tt.phase_code
          and dsd.course_code = tt.course_code
        limit 1)                                                            as course_name
     , tt.grade_code
     , (select dsd.node_name
        from data_school_department dsd
        where dsd.deleted_status = 0
          and dsd.node_type = 3
          and dsd.school_code = tt.school_code
          and dsd.phase_code = tt.phase_code
          and dsd.course_code = tt.course_code
          and dsd.grade_code = tt.grade_code
        limit 1)                                                            as grade_name

     , tt.bill_phase_code
     , tt.bill_type
     , tt.has_valid_bill
     , tt.has_finish_bill
     , tt.xzf_yj + tt.xzf_sk_c                                              as xzf_yj
     , tt.xzf_dk
     , tt.xzf_sk
     , tt.xzf_tk
     , tt.hsf_yj + tt.hsf_sk_c                                            as hsf_yj
     , tt.hsf_dk
     , tt.hsf_sk
     , tt.hsf_tk
     , tt.zsf_yj + tt.zsf_sk_c                                             as zsf_yj
     , tt.zsf_dk
     , tt.zsf_sk
     , tt.zsf_tk
     , tt.xcf_yj + tt.xcf_sk_c                                             as xcf_yj
     , tt.xcf_dk
     , tt.xcf_sk
     , tt.xcf_tk
     , tt.qt_yj + tt.qt_sk_c                                              as qt_yj
     , tt.qt_dk
     , tt.qt_sk
     , tt.qt_tk
     , tt.my                                                                as manyi_all
     , tt.xzf_yj + tt.xzf_sk_c - tt.xzf_tk                                  as xzf_yijiao
     , tt.hsf_yj + tt.hsf_sk_c - tt.hsf_tk                                  as hsf_yijiao
     , tt.zsf_yj + tt.zsf_sk_c - tt.zsf_tk                                  as zsf_yijiao
     , tt.xcf_yj + tt.xcf_sk_c - tt.xcf_tk                                  as xcf_yijiao
     , tt.qt_yj + tt.qt_sk_c - tt.qt_tk                                     as qt_yijiao
     , tt.xzf_yj + tt.xzf_sk_c - tt.xzf_tk + tt.hsf_yj + tt.hsf_sk_c - tt.hsf_tk + tt.zsf_yj + tt.zsf_sk_c - tt.zsf_tk +
       tt.xcf_yj + tt.xcf_sk_c - tt.xcf_tk + tt.qt_yj + tt.qt_sk_c - tt.qt_tk                      as yingjiao_all
     ,
    tt.xzf_dk + tt.xzf_sk - tt.xzf_tk + tt.hsf_dk + tt.hsf_sk - tt.hsf_tk + tt.zsf_dk + tt.zsf_sk - tt.zsf_tk +
    tt.xcf_dk + tt.xcf_sk - tt.xcf_tk + tt.qt_dk + tt.qt_sk - tt.qt_tk      as yijiao_all
     , (tt.xzf_yj + tt.xzf_sk_c - tt.xzf_tk + tt.hsf_yj + tt.hsf_sk_c - tt.hsf_tk + tt.zsf_yj + tt.zsf_sk_c - tt.zsf_tk +
        tt.xcf_yj + tt.xcf_sk_c - tt.xcf_tk + tt.qt_yj + tt.qt_sk_c - tt.qt_tk ) -
       (tt.xzf_dk + tt.xzf_sk - tt.xzf_tk + tt.hsf_dk + tt.hsf_sk - tt.hsf_tk + tt.zsf_dk + tt.zsf_sk -
        tt.zsf_tk +
        tt.xcf_dk + tt.xcf_sk - tt.xcf_tk + tt.qt_dk + tt.qt_sk - tt.qt_tk) as weijiao_all
     , tt.xzf_dk + tt.hsf_dk + tt.zsf_dk + tt.xcf_dk + tt.qt_dk             as dikou_all
     , tt.xzf_sk + tt.hsf_sk + tt.zsf_sk + tt.xcf_sk + tt.qt_sk             as shoukuan_all
     , tt.xzf_tk + tt.hsf_tk + tt.zsf_tk + tt.xcf_tk + tt.qt_tk             as tuikuan_all
from (
# 学校本校生统计
         select 'local'                                                                            as type
              , cs.student_id
              , cs.student_code
              , cs.student_name
              , cs.parent_identity
              , csc.school_code
              , (select ds.school_name
                 from data_school ds
                 where ds.school_code = csc.school_code)                                           as school_name
              , csc.year_term_code
              , dyt.year_start
              , dyt.term_code
              , concat(dyt.year_start, '-', dyt.year_end, '学年第', dyt.term_code,
                       '学期')                                                                     as year_term_name
              , csc.student_status
              , csc.phase_code
              , csc.course_code
              , csc.grade_code
              , (select GROUP_CONCAT(distinct dsd.node_name separator ',')
                 from bill_student bs
                          inner join data_school_department dsd
                                     on dsd.deleted_status = 0 and dsd.node_type = 1 and
                                        dsd.school_code = bs.school_code and dsd.phase_code = bs.phase_code
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.status in (50, 60, 70, 80))                                              as bill_phase_code
              , (select GROUP_CONCAT(distinct case when bill_type = 'TERM' then '学期' else '月度' end separator
                                     ',')
                 from bill_student bs
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.status in (50, 60, 70, 80))                                              as bill_type
              , (select if(count(1) > 0, true, false)
                 from bill_student bs
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.status in (50, 60, 70, 80))                                              as has_valid_bill
              , (select if(count(1) > 0, false, true)
                 from bill_student bs
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.is_closed = 0
                   and bs.status in (50, 60))                                                      as has_finish_bill
              #      未关闭的待缴费之后的账单应缴金额
              , (select ifnull(sum(bscp.need_paid_cost), 0)
                 from bill_student bs
                          inner join bill_student_charging_project bscp
                                     on bs.bill_student_code = bscp.student_bill_code
                                         and bscp.charging_category_code in
                                             ('00001', 'BC0007', 'BC0018', 'BC0022', 'BC0020')
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.is_closed = 0
                   and bs.status in (50, 60, 70, 80))                                              as xzf_yj
#      已关闭的待缴费之后的账单收款金额
              , (select ifnull(sum(bcd.collection_amount), 0)
                 from bill_student bs
                          inner join bill_collection bc
                                     on bs.bill_student_code = bc.bill_student_code and bc.status = 10
                          inner join bill_collection_detail bcd on bc.id = bcd.collection_id
                     and bcd.charging_category_code in ('00001', 'BC0007', 'BC0018', 'BC0022', 'BC0020') and
                                                                   bcd.collection_amount >= 0
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.is_closed = 1
                   and bs.status in (50, 60, 70, 80))                                              as xzf_sk_c
              #      待缴费之后的账单抵扣金额 （收款方式=余额支付）
              , (select ifnull(sum(bcd.collection_amount), 0)
                 from bill_student bs
                          inner join bill_collection bc
                                     on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
                                        bc.collection_type = 'BalanceDeduction'
                          inner join bill_collection_detail bcd on bc.id = bcd.collection_id
                     and bcd.charging_category_code in ('00001', 'BC0007', 'BC0018', 'BC0022', 'BC0020') and
                                                                   bcd.collection_amount >= 0
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.status in (50, 60, 70, 80))                                              as xzf_dk
              #      待缴费之后的账单收款金额 （收款方式!=余额支付）
              , (select ifnull(sum(bcd.collection_amount), 0)
                 from bill_student bs
                          inner join bill_collection bc
                                     on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
                                        bc.collection_type != 'BalanceDeduction'
                          inner join bill_collection_detail bcd on bc.id = bcd.collection_id
                     and bcd.charging_category_code in ('00001', 'BC0007', 'BC0018', 'BC0022', 'BC0020') and
                                                                   bcd.collection_amount >= 0
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.status in (50, 60, 70, 80))                                              as xzf_sk
              #      待缴费之后的账单内退款金额
              , (select ifnull(abs(sum(bcd.collection_amount)), 0)
                 from bill_student bs
                          inner join bill_collection bc
                                     on bs.bill_student_code = bc.bill_student_code and bc.status = 10
                          inner join bill_collection_detail bcd on bc.id = bcd.collection_id
                     and bcd.charging_category_code in ('00001', 'BC0007', 'BC0018', 'BC0022', 'BC0020') and
                                                                   bcd.collection_amount < 0
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.status in (50, 60, 70, 80))                                              as xzf_tk
              , (select ifnull(sum(bscp.need_paid_cost), 0)
                 from bill_student bs
                          inner join bill_student_charging_project bscp
                                     on bs.bill_student_code = bscp.student_bill_code
                                         and bscp.charging_category_code in ('00003')
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.is_closed = 0
                   and bs.status in (50, 60, 70, 80))                                              as hsf_yj
              , (select ifnull(sum(bcd.collection_amount), 0)
                 from bill_student bs
                          inner join bill_collection bc
                                     on bs.bill_student_code = bc.bill_student_code and bc.status = 10
                          inner join bill_collection_detail bcd on bc.id = bcd.collection_id
                     and bcd.charging_category_code in ('00003') and bcd.collection_amount >= 0
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.is_closed = 1
                   and bs.status in (50, 60, 70, 80))                                              as hsf_sk_c
              , (select ifnull(sum(bcd.collection_amount), 0)
                 from bill_student bs
                          inner join bill_collection bc
                                     on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
                                        bc.collection_type = 'BalanceDeduction'
                          inner join bill_collection_detail bcd on bc.id = bcd.collection_id
                     and bcd.charging_category_code in ('00003') and bcd.collection_amount >= 0
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.status in (50, 60, 70, 80))                                              as hsf_dk
              , (select ifnull(sum(bcd.collection_amount), 0)
                 from bill_student bs
                          inner join bill_collection bc
                                     on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
                                        bc.collection_type != 'BalanceDeduction'
                          inner join bill_collection_detail bcd on bc.id = bcd.collection_id
                     and bcd.charging_category_code in ('00003') and bcd.collection_amount >= 0
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.status in (50, 60, 70, 80))                                              as hsf_sk
              , (select ifnull(abs(sum(bcd.collection_amount)), 0)
                 from bill_student bs
                          inner join bill_collection bc
                                     on bs.bill_student_code = bc.bill_student_code and bc.status = 10
                          inner join bill_collection_detail bcd on bc.id = bcd.collection_id
                     and bcd.charging_category_code in ('00003') and bcd.collection_amount < 0
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.status in (50, 60, 70, 80))                                              as hsf_tk
              , (select ifnull(sum(bscp.need_paid_cost), 0)
                 from bill_student bs
                          inner join bill_student_charging_project bscp
                                     on bs.bill_student_code = bscp.student_bill_code
                                         and bscp.charging_category_code in ('00002', 'BC0023')
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.is_closed = 0
                   and bs.status in (50, 60, 70, 80))                                              as zsf_yj
              , (select ifnull(sum(bcd.collection_amount), 0)
                 from bill_student bs
                          inner join bill_collection bc
                                     on bs.bill_student_code = bc.bill_student_code and bc.status = 10
                          inner join bill_collection_detail bcd on bc.id = bcd.collection_id
                     and bcd.charging_category_code in ('00002', 'BC0023') and bcd.collection_amount >= 0
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.is_closed = 1
                   and bs.status in (50, 60, 70, 80))                                              as zsf_sk_c
              , (select ifnull(sum(bcd.collection_amount), 0)
                 from bill_student bs
                          inner join bill_collection bc
                                     on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
                                        bc.collection_type = 'BalanceDeduction'
                          inner join bill_collection_detail bcd on bc.id = bcd.collection_id
                     and bcd.charging_category_code in ('00002', 'BC0023') and bcd.collection_amount >= 0
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.status in (50, 60, 70, 80))                                              as zsf_dk
              , (select ifnull(sum(bcd.collection_amount), 0)
                 from bill_student bs
                          inner join bill_collection bc
                                     on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
                                        bc.collection_type != 'BalanceDeduction'
                          inner join bill_collection_detail bcd on bc.id = bcd.collection_id
                     and bcd.charging_category_code in ('00002', 'BC0023') and bcd.collection_amount >= 0
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.status in (50, 60, 70, 80))                                              as zsf_sk
              , (select ifnull(abs(sum(bcd.collection_amount)), 0)
                 from bill_student bs
                          inner join bill_collection bc
                                     on bs.bill_student_code = bc.bill_student_code and bc.status = 10
                          inner join bill_collection_detail bcd on bc.id = bcd.collection_id
                     and bcd.charging_category_code in ('00002', 'BC0023') and bcd.collection_amount < 0
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.status in (50, 60, 70, 80))                                              as zsf_tk
              # 校车费
              , (select ifnull(sum(bscp.need_paid_cost), 0)
                 from bill_student bs
                          inner join bill_student_charging_project bscp
                                     on bs.bill_student_code = bscp.student_bill_code
                                         and bscp.charging_category_code in ('00005')
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.is_closed = 0
                   and bs.status in (50, 60, 70, 80))                                              as xcf_yj
              , (select ifnull(sum(bcd.collection_amount), 0)
                 from bill_student bs
                          inner join bill_collection bc
                                     on bs.bill_student_code = bc.bill_student_code and bc.status = 10
                          inner join bill_collection_detail bcd on bc.id = bcd.collection_id
                     and bcd.charging_category_code in ('00005') and bcd.collection_amount >= 0
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.is_closed = 1
                   and bs.status in (50, 60, 70, 80))                                              as xcf_sk_c
              , (select ifnull(sum(bcd.collection_amount), 0)
                 from bill_student bs
                          inner join bill_collection bc
                                     on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
                                        bc.collection_type = 'BalanceDeduction'
                          inner join bill_collection_detail bcd on bc.id = bcd.collection_id
                     and bcd.charging_category_code in ('00005') and bcd.collection_amount >= 0
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.status in (50, 60, 70, 80))                                              as xcf_dk
              , (select ifnull(sum(bcd.collection_amount), 0)
                 from bill_student bs
                          inner join bill_collection bc
                                     on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
                                        bc.collection_type != 'BalanceDeduction'
                          inner join bill_collection_detail bcd on bc.id = bcd.collection_id
                     and bcd.charging_category_code in ('00005') and bcd.collection_amount >= 0
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.status in (50, 60, 70, 80))                                              as xcf_sk
              , (select ifnull(abs(sum(bcd.collection_amount)), 0)
                 from bill_student bs
                          inner join bill_collection bc
                                     on bs.bill_student_code = bc.bill_student_code and bc.status = 10
                          inner join bill_collection_detail bcd on bc.id = bcd.collection_id
                     and bcd.charging_category_code in ('00005') and bcd.collection_amount < 0
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.status in (50, 60, 70, 80))                                              as xcf_tk
              , (select ifnull(sum(bscp.need_paid_cost), 0)
                 from bill_student bs
                          inner join bill_student_charging_project bscp
                                     on bs.bill_student_code = bscp.student_bill_code
                                         and bscp.charging_category_code not in
                                             ('00001', 'BC0007', 'BC0018', 'BC0022', 'BC0020', '00003', '00002',
                                              'BC0023', '00005')
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.is_closed = 0
                   and bs.status in (50, 60, 70, 80))                                              as qt_yj
              , (select ifnull(sum(bcd.collection_amount), 0)
                 from bill_student bs
                          inner join bill_collection bc
                                     on bs.bill_student_code = bc.bill_student_code and bc.status = 10
                          inner join bill_collection_detail bcd on bc.id = bcd.collection_id
                     and bcd.charging_category_code not in
                         ('00001', 'BC0007', 'BC0018', 'BC0022', 'BC0020', '00003', '00002', 'BC0023',
                          '00005') and
                                                                   bcd.collection_amount >= 0
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.is_closed = 1
                   and bs.status in (50, 60, 70, 80))                                              as qt_sk_c
              , (select ifnull(sum(bcd.collection_amount), 0)
                 from bill_student bs
                          inner join bill_collection bc
                                     on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
                                        bc.collection_type = 'BalanceDeduction'
                          inner join bill_collection_detail bcd on bc.id = bcd.collection_id
                     and bcd.charging_category_code not in
                         ('00001', 'BC0007', 'BC0018', 'BC0022', 'BC0020', '00003', '00002', 'BC0023',
                          '00005') and
                                                                   bcd.collection_amount >= 0
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.status in (50, 60, 70, 80))                                              as qt_dk
              , (select ifnull(sum(bcd.collection_amount), 0)
                 from bill_student bs
                          inner join bill_collection bc
                                     on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
                                        bc.collection_type != 'BalanceDeduction'
                          inner join bill_collection_detail bcd on bc.id = bcd.collection_id
                     and bcd.charging_category_code not in
                         ('00001', 'BC0007', 'BC0018', 'BC0022', 'BC0020', '00003', '00002', 'BC0023',
                          '00005') and
                                                                   bcd.collection_amount >= 0
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.status in (50, 60, 70, 80))                                              as qt_sk
              , (select ifnull(abs(sum(bcd.collection_amount)), 0)
                 from bill_student bs
                          inner join bill_collection bc
                                     on bs.bill_student_code = bc.bill_student_code and bc.status = 10
                          inner join bill_collection_detail bcd on bc.id = bcd.collection_id
                     and bcd.charging_category_code not in
                         ('00001', 'BC0007', 'BC0018', 'BC0022', 'BC0020', '00003', '00002', 'BC0023',
                          '00005') and
                                                                   bcd.collection_amount < 0
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.status in (50, 60, 70, 80))                                              as qt_tk
              , (select ifnull(sum(bc.collection_amount - bc.collection_entry_amount), 0)
                 from bill_student bs
                          inner join bill_collection bc
                                     on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
                                        bc.collection_amount >= 0
                 where bs.school_code = csc.school_code
                   and bs.year_term_code = csc.year_term_code
                   and bs.student_id = cs.student_id
                   and bs.status in (50, 60, 70, 80))                                              as my
         from core_student cs
                  inner join core_student_current csc on cs.student_id = csc.student_id
                  inner join data_year_term dyt
                             on dyt.school_code = csc.school_code and dyt.year_term_code = csc.year_term_code
         where csc.school_code = ${schoolCode}
           and csc.year_term_code = ${yearTermCode}
           and exists(select 1
                      from bill_student bs
                      where bs.school_code = csc.school_code
                        and bs.year_term_code = csc.year_term_code
                        and bs.student_id = cs.student_id
                        and bs.status in (50, 60, 70, 80))
#          union all
# # 学校跨校账单统计
#          select 'cross'                                                                            as type
#               , cs.student_id
#               , cs.student_code
#               , cs.student_name
#               , cs.parent_identity
#               , csc.school_code
#               , (select ds.school_name
#                  from data_school ds
#                  where ds.school_code = csc.school_code)                                           as school_name
#               , csc.year_term_code
#               , dyt.year_start
#               , dyt.term_code
#               , concat(dyt.year_start, '-', dyt.year_end, '学年第', dyt.term_code,
#                        '学期')                                                                     as year_term_name
#               , 0                                                                                  as student_status
#               , csc.phase_code
#               , csc.course_code
#               , csc.grade_code
#               , (select GROUP_CONCAT(distinct dsd.node_name separator ',')
#                  from bill_student bs
#                           inner join data_school_department dsd
#                                      on dsd.deleted_status = 0 and dsd.node_type = 1 and
#                                         dsd.school_code = bs.school_code and dsd.phase_code = bs.phase_code
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.status in (50, 60, 70, 80))                                              as bill_phase_code
#               , (select GROUP_CONCAT(distinct case when bill_type = 'TERM' then '学期' else '月度' end separator
#                                      ',')
#                  from bill_student bs
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.status in (50, 60, 70, 80))                                              as bill_type
#               , (select if(count(1) > 0, true, false)
#                  from bill_student bs
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.status in (50, 60, 70, 80))                                              as has_valid_bill
#               , (select if(count(1) > 0, false, true)
#                  from bill_student bs
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.is_closed = 0
#                    and bs.status in (50, 60))                                                      as has_finish_bill
#               #      未关闭的待缴费之后的账单应缴金额
#               , (select ifnull(sum(bscp.need_paid_cost), 0)
#                  from bill_student bs
#                           inner join bill_student_charging_project bscp
#                                      on bs.bill_student_code = bscp.student_bill_code
#                                          and bscp.charging_category_code in
#                                              ('00001', 'BC0007', 'BC0018', 'BC0022', 'BC0020')
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.is_closed = 0
#                    and bs.status in (50, 60, 70, 80))                                              as xzf_yj
# #      已关闭的待缴费之后的账单收款金额
#               , (select ifnull(sum(bcd.collection_amount), 0)
#                  from bill_student bs
#                           inner join bill_collection bc
#                                      on bs.bill_student_code = bc.bill_student_code and bc.status = 10
#                           inner join bill_collection_detail bcd on bc.id = bcd.collection_id
#                      and bcd.charging_category_code in ('00001', 'BC0007', 'BC0018', 'BC0022', 'BC0020') and
#                                                                    bcd.collection_amount >= 0
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.is_closed = 1
#                    and bs.status in (50, 60, 70, 80))                                              as xzf_sk_c
#               #      待缴费之后的账单抵扣金额 （收款方式=余额支付）
#               , (select ifnull(sum(bcd.collection_amount), 0)
#                  from bill_student bs
#                           inner join bill_collection bc
#                                      on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
#                                         bc.collection_type = 'BalanceDeduction'
#                           inner join bill_collection_detail bcd on bc.id = bcd.collection_id
#                      and bcd.charging_category_code in ('00001', 'BC0007', 'BC0018', 'BC0022', 'BC0020') and
#                                                                    bcd.collection_amount >= 0
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.status in (50, 60, 70, 80))                                              as xzf_dk
#               #      待缴费之后的账单收款金额 （收款方式!=余额支付）
#               , (select ifnull(sum(bcd.collection_amount), 0)
#                  from bill_student bs
#                           inner join bill_collection bc
#                                      on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
#                                         bc.collection_type != 'BalanceDeduction'
#                           inner join bill_collection_detail bcd on bc.id = bcd.collection_id
#                      and bcd.charging_category_code in ('00001', 'BC0007', 'BC0018', 'BC0022', 'BC0020') and
#                                                                    bcd.collection_amount >= 0
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.status in (50, 60, 70, 80))                                              as xzf_sk
#               #      待缴费之后的账单内退款金额
#               , (select ifnull(abs(sum(bcd.collection_amount)), 0)
#                  from bill_student bs
#                           inner join bill_collection bc
#                                      on bs.bill_student_code = bc.bill_student_code and bc.status = 10
#                           inner join bill_collection_detail bcd on bc.id = bcd.collection_id
#                      and bcd.charging_category_code in ('00001', 'BC0007', 'BC0018', 'BC0022', 'BC0020') and
#                                                                    bcd.collection_amount < 0
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.status in (50, 60, 70, 80))                                              as xzf_tk
#               , (select ifnull(sum(bscp.need_paid_cost), 0)
#                  from bill_student bs
#                           inner join bill_student_charging_project bscp
#                                      on bs.bill_student_code = bscp.student_bill_code
#                                          and bscp.charging_category_code in ('00003')
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.is_closed = 0
#                    and bs.status in (50, 60, 70, 80))                                              as hsf_yj
#               , (select ifnull(sum(bcd.collection_amount), 0)
#                  from bill_student bs
#                           inner join bill_collection bc
#                                      on bs.bill_student_code = bc.bill_student_code and bc.status = 10
#                           inner join bill_collection_detail bcd on bc.id = bcd.collection_id
#                      and bcd.charging_category_code in ('00003') and bcd.collection_amount >= 0
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.is_closed = 1
#                    and bs.status in (50, 60, 70, 80))                                              as hsf_sk_c
#               , (select ifnull(sum(bcd.collection_amount), 0)
#                  from bill_student bs
#                           inner join bill_collection bc
#                                      on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
#                                         bc.collection_type = 'BalanceDeduction'
#                           inner join bill_collection_detail bcd on bc.id = bcd.collection_id
#                      and bcd.charging_category_code in ('00003') and bcd.collection_amount >= 0
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.status in (50, 60, 70, 80))                                              as hsf_dk
#               , (select ifnull(sum(bcd.collection_amount), 0)
#                  from bill_student bs
#                           inner join bill_collection bc
#                                      on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
#                                         bc.collection_type != 'BalanceDeduction'
#                           inner join bill_collection_detail bcd on bc.id = bcd.collection_id
#                      and bcd.charging_category_code in ('00003') and bcd.collection_amount >= 0
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.status in (50, 60, 70, 80))                                              as hsf_sk
#               , (select ifnull(abs(sum(bcd.collection_amount)), 0)
#                  from bill_student bs
#                           inner join bill_collection bc
#                                      on bs.bill_student_code = bc.bill_student_code and bc.status = 10
#                           inner join bill_collection_detail bcd on bc.id = bcd.collection_id
#                      and bcd.charging_category_code in ('00003') and bcd.collection_amount < 0
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.status in (50, 60, 70, 80))                                              as hsf_tk
#               , (select ifnull(sum(bscp.need_paid_cost), 0)
#                  from bill_student bs
#                           inner join bill_student_charging_project bscp
#                                      on bs.bill_student_code = bscp.student_bill_code
#                                          and bscp.charging_category_code in ('00002', 'BC0023')
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.is_closed = 0
#                    and bs.status in (50, 60, 70, 80))                                              as zsf_yj
#               , (select ifnull(sum(bcd.collection_amount), 0)
#                  from bill_student bs
#                           inner join bill_collection bc
#                                      on bs.bill_student_code = bc.bill_student_code and bc.status = 10
#                           inner join bill_collection_detail bcd on bc.id = bcd.collection_id
#                      and bcd.charging_category_code in ('00002', 'BC0023') and bcd.collection_amount >= 0
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.is_closed = 1
#                    and bs.status in (50, 60, 70, 80))                                              as zsf_sk_c
#               , (select ifnull(sum(bcd.collection_amount), 0)
#                  from bill_student bs
#                           inner join bill_collection bc
#                                      on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
#                                         bc.collection_type = 'BalanceDeduction'
#                           inner join bill_collection_detail bcd on bc.id = bcd.collection_id
#                      and bcd.charging_category_code in ('00002', 'BC0023') and bcd.collection_amount >= 0
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.status in (50, 60, 70, 80))                                              as zsf_dk
#               , (select ifnull(sum(bcd.collection_amount), 0)
#                  from bill_student bs
#                           inner join bill_collection bc
#                                      on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
#                                         bc.collection_type != 'BalanceDeduction'
#                           inner join bill_collection_detail bcd on bc.id = bcd.collection_id
#                      and bcd.charging_category_code in ('00002', 'BC0023') and bcd.collection_amount >= 0
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.status in (50, 60, 70, 80))                                              as zsf_sk
#               , (select ifnull(abs(sum(bcd.collection_amount)), 0)
#                  from bill_student bs
#                           inner join bill_collection bc
#                                      on bs.bill_student_code = bc.bill_student_code and bc.status = 10
#                           inner join bill_collection_detail bcd on bc.id = bcd.collection_id
#                      and bcd.charging_category_code in ('00002', 'BC0023') and bcd.collection_amount < 0
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.status in (50, 60, 70, 80))                                              as zsf_tk
#               # 校车费
#               , (select ifnull(sum(bscp.need_paid_cost), 0)
#                  from bill_student bs
#                           inner join bill_student_charging_project bscp
#                                      on bs.bill_student_code = bscp.student_bill_code
#                                          and bscp.charging_category_code in ('00005')
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.is_closed = 0
#                    and bs.status in (50, 60, 70, 80))                                              as xcf_yj
#               , (select ifnull(sum(bcd.collection_amount), 0)
#                  from bill_student bs
#                           inner join bill_collection bc
#                                      on bs.bill_student_code = bc.bill_student_code and bc.status = 10
#                           inner join bill_collection_detail bcd on bc.id = bcd.collection_id
#                      and bcd.charging_category_code in ('00005') and bcd.collection_amount >= 0
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.is_closed = 1
#                    and bs.status in (50, 60, 70, 80))                                              as xcf_sk_c
#               , (select ifnull(sum(bcd.collection_amount), 0)
#                  from bill_student bs
#                           inner join bill_collection bc
#                                      on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
#                                         bc.collection_type = 'BalanceDeduction'
#                           inner join bill_collection_detail bcd on bc.id = bcd.collection_id
#                      and bcd.charging_category_code in ('00005') and bcd.collection_amount >= 0
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.status in (50, 60, 70, 80))                                              as xcf_dk
#               , (select ifnull(sum(bcd.collection_amount), 0)
#                  from bill_student bs
#                           inner join bill_collection bc
#                                      on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
#                                         bc.collection_type != 'BalanceDeduction'
#                           inner join bill_collection_detail bcd on bc.id = bcd.collection_id
#                      and bcd.charging_category_code in ('00005') and bcd.collection_amount >= 0
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.status in (50, 60, 70, 80))                                              as xcf_sk
#               , (select ifnull(abs(sum(bcd.collection_amount)), 0)
#                  from bill_student bs
#                           inner join bill_collection bc
#                                      on bs.bill_student_code = bc.bill_student_code and bc.status = 10
#                           inner join bill_collection_detail bcd on bc.id = bcd.collection_id
#                      and bcd.charging_category_code in ('00005') and bcd.collection_amount < 0
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.status in (50, 60, 70, 80))                                              as xcf_tk
#               , (select ifnull(sum(bscp.need_paid_cost), 0)
#                  from bill_student bs
#                           inner join bill_student_charging_project bscp
#                                      on bs.bill_student_code = bscp.student_bill_code
#                                          and bscp.charging_category_code not in
#                                              ('00001', 'BC0007', 'BC0018', 'BC0022', 'BC0020', '00003', '00002',
#                                               'BC0023', '00005')
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.is_closed = 0
#                    and bs.status in (50, 60, 70, 80))                                              as qt_yj
#               , (select ifnull(sum(bcd.collection_amount), 0)
#                  from bill_student bs
#                           inner join bill_collection bc
#                                      on bs.bill_student_code = bc.bill_student_code and bc.status = 10
#                           inner join bill_collection_detail bcd on bc.id = bcd.collection_id
#                      and bcd.charging_category_code not in
#                          ('00001', 'BC0007', 'BC0018', 'BC0022', 'BC0020', '00003', '00002', 'BC0023',
#                           '00005') and
#                                                                    bcd.collection_amount >= 0
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.is_closed = 1
#                    and bs.status in (50, 60, 70, 80))                                              as qt_sk_c
#               , (select ifnull(sum(bcd.collection_amount), 0)
#                  from bill_student bs
#                           inner join bill_collection bc
#                                      on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
#                                         bc.collection_type = 'BalanceDeduction'
#                           inner join bill_collection_detail bcd on bc.id = bcd.collection_id
#                      and bcd.charging_category_code not in
#                          ('00001', 'BC0007', 'BC0018', 'BC0022', 'BC0020', '00003', '00002', 'BC0023',
#                           '00005') and
#                                                                    bcd.collection_amount >= 0
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.status in (50, 60, 70, 80))                                              as qt_dk
#               , (select ifnull(sum(bcd.collection_amount), 0)
#                  from bill_student bs
#                           inner join bill_collection bc
#                                      on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
#                                         bc.collection_type != 'BalanceDeduction'
#                           inner join bill_collection_detail bcd on bc.id = bcd.collection_id
#                      and bcd.charging_category_code not in
#                          ('00001', 'BC0007', 'BC0018', 'BC0022', 'BC0020', '00003', '00002', 'BC0023',
#                           '00005') and
#                                                                    bcd.collection_amount >= 0
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.status in (50, 60, 70, 80))                                              as qt_sk
#               , (select ifnull(abs(sum(bcd.collection_amount)), 0)
#                  from bill_student bs
#                           inner join bill_collection bc
#                                      on bs.bill_student_code = bc.bill_student_code and bc.status = 10
#                           inner join bill_collection_detail bcd on bc.id = bcd.collection_id
#                      and bcd.charging_category_code not in
#                          ('00001', 'BC0007', 'BC0018', 'BC0022', 'BC0020', '00003', '00002', 'BC0023',
#                           '00005') and
#                                                                    bcd.collection_amount < 0
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.status in (50, 60, 70, 80))                                              as qt_tk
#               , (select ifnull(sum(bc.collection_amount - bc.collection_entry_amount), 0)
#                  from bill_student bs
#                           inner join bill_collection bc
#                                      on bs.bill_student_code = bc.bill_student_code and bc.status = 10 and
#                                         bc.collection_amount >= 0
#                  where bs.school_code = csc.school_code
#                    and bs.year_term_code = csc.year_term_code
#                    and bs.student_id = cs.student_id
#                    and bs.status in (50, 60, 70, 80))                                              as my
#          from (select bs.student_id
#                     , bs.school_code
#                     , bs.year_term_code
#                     , min(bs.phase_code)  as phase_code
#                     , min(bs.course_code) as course_code
#                     , min(bs.grade_code)  as grade_code
#                from bill_student bs
#                where bs.status in (50, 60, 70, 80)
#                  and bs.school_code = ${schoolCode}
#                  and bs.year_term_code = ${yearTermCode}
#                  and not exists(select 1
#                                 from core_student_current csc
#                                 where bs.student_id = csc.student_id
#                                   and csc.school_code = bs.school_code
#                                   and bs.year_term_code = csc.year_term_code)
#                group by bs.student_id, bs.school_code, bs.year_term_code) csc
#                   inner join core_student cs on cs.student_id = csc.student_id
#                   inner join data_year_term dyt
#                              on dyt.school_code = csc.school_code and
#                                 dyt.year_term_code = csc.year_term_code
                                ) tt
# and cs.student_code = 'N202402000088';
where tt.student_code = 'N202402000088';