Explain the following text.



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';