Skip to content
TheChatArchiveThe Chat Archive
gpt-4o-mini-2024-07-18· OpenAIAi2 WildChat-4.8M (AI2)

Explain the following text. select tt.type , tt.studentid , tt.studentcode ,…

  1. User

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

  2. Assistant

    The text you've provided is an extensive SQL query that retrieves and calculates various data related to students, specifically in a school setting. Below, I will break down and summarize the key components and functionalities of the query:

    Overview

    This SQL query aims to gather information on students, their identities, and their financial data, including any outstanding bills or fees. It predominantly focuses on students from a specific school and term, rounding out with financial calculations that provide a comprehensive overview of each student's billing situation.

    Main Components

    1. Selection:

      • The main selection starts with select and lists various fields, including student IDs, names, school details, and their status.
      • The query uses sub-queries to fetch descriptive names from lookup tables such as data_dict_data which provides human-readable names for parent identities and student statuses based on codes.
    2. Financial Calculations:

      • Several calculations aggregate financial data corresponding to different types of fees students may encounter, including:
        • xzf_yj: Expected amounts (应缴) for various fees.
        • xzf_sk_c: Collected cash (收款) for various fees.
        • xzf_tk: Refund amounts (退款).
        • Similar calculations are performed for other categories of fees (hsf, zsf, xcf, qt).
      • The query computes total amounts due, collected amounts, refunds, and outstanding amounts through a series of aggregations, ensuring all necessary financial data is reflected.
    3. Subqueries:

      • Several subselect statements are used extensively to derive additional data from tables related to bills (bill_student, bill_collection, bill_student_charging_project), ensuring that only the relevant financial records for the specified school and year are fetched.
      • These subqueries count and sum financial transactions, allowing the outer query to compile a detailed financial profile for each student.
    4. Logical Conditions:

      • The from clause within the main query starts from a derived table (tt), which collects students and their billing information for a particular school and year term.
      • Different filtering conditions, such as school_code, year_term_code, and student IDs are employed to focus the data correctly.
    5. Final Filtering:

      • The final where clause filters results to show only the record for a specific student with a known student code ('N202402000088').

    Conclusion

    In summary, the provided SQL query is constructed to extract a comprehensive report on a student, including their identity, school specifics, term data, and detailed financial metrics related to fees and invoices. By using multiple inner queries and aggregate functions, it effectively constructs a holistic view of a student's financial obligations, making it an essential tool for school administrators or financial officers managing student accounts.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.