Explain the following text. select tt.type , tt.studentid , tt.studentcode ,…
- 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_allfrom (
学校本校生统计
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
) ttand cs.student_code = 'N202402000088';
where tt.student_code = 'N202402000088';
- 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
-
Selection:
- The main selection starts with
selectand 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_datawhich provides human-readable names for parent identities and student statuses based on codes.
- The main selection starts with
-
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.
- Several calculations aggregate financial data corresponding to different types of fees students may encounter, including:
-
Subqueries:
- Several
subselectstatements 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.
- Several
-
Logical Conditions:
- The
fromclause 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.
- The
-
Final Filtering:
- The final
whereclause filters results to show only the record for a specific student with a known student code ('N202402000088').
- The final
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.
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.