通过使用存储过程,使用数据库记录获取输出 [英] By using store procedure get the output by using the database records
问题描述
我有四张桌子如下;
选择 * 来自 course_registration;
选择 * 来自 batch_course_registration;
选择 * 来自 bill_file;
从 receipt_file中选择 * ;
选择 * 来自 course_registration;
(记录如下);
cr_bill_no stud_id cr_bill_dt cmn_minor_code cr_Active
1 1 2004-04-07 MFA A
2 2 2004-04-07 MFA A
3 3 2004-04-07 MFA A
4 4 2004-04-07 MFA A
5 5 2004-04-07 MFA A
从 batch_course_registration中选择 * ;
(记录如下);
bcr_reg_no cr_bill_no bcr_batch_id
APS / B / 57 / 53247/2011 1 B8753
PH1 / B / 11/49243/5/2011 2 B8752
TASCO / B / 74/15427/2011 3 B8745
REO / B / 103/25174/5 / 2011 4 B8749
CHEMCO / B / 44/36334/2011 5 B8774
从 bill_file中选择 * ;
(记录如下);
bill_no cr_bill_no bill_dt bill_pend_Amt bill_active
443 1 2011-04-22 50 A
445 2 2011-04-29 190 A
446 3 2011-05-02 300 A
447 4 2011-05-09 450 A
448 5 2011-05-09 500 A
从 receipt_file中选择 * ;
(记录如下);
rcpt_no bill_no rcpt_dt Amt
512 443 2011-04-22 450
513 445 2011-04-29 600
514 446 2011-05-02 890
516 447 2011 -05-09 950
516 448 2011-05-09 1050
使用四张桌子并使用商店程序我想得到以下输出如下,
我如何使用商店程序低于输出。
stud_id bcr_batch_id cr_bill_no bill_no bill_dt bill_pend_Amt bill_active rcpt_no rcpt_dt Amt
1 B8753 1 443 2011-04-22 50 A 512 2011-04-22 450
已添加代码块 - OriginalGriff [/ edit]
SELECT course_registration.stud_id,
batch_course_registration.bcr_batch_id ,
bill_file.cr_bill_no,
receipt_file.bill_no,
bill_file.bill_dt,
bill_file.bill_pend_Amt,
bill_file.bill_active,
receipt_file.rcpt_no,
receipt_file.rcpt_dt,Amt
FROM course_registration cr
inner join batch_course_registration bcr on cr.cr_bill_no = crb.cr_bill_no
内部 join bill_file bf on bf.cr_bill_no = crb.cr_bill_no
< span class =code-keyword> inner join receipt_file rf on rf.bill_no = bf.bill_no
WHERE c r.stud_id =<写在这里学生ID>
顺便说一下你在做什么时会遇到什么问题?
I have four tables as follows;
select * from course_registration;
select * from batch_course_registration;
select * from bill_file;
select * from receipt_file;
select * from course_registration;
(records as follows);
cr_bill_no stud_id cr_bill_dt cmn_minor_code cr_Active
1 1 2004-04-07 MFA A
2 2 2004-04-07 MFA A
3 3 2004-04-07 MFA A
4 4 2004-04-07 MFA A
5 5 2004-04-07 MFA A
select * from batch_course_registration;
(records as follows);
bcr_reg_no cr_bill_no bcr_batch_id
APS/B/57/53247/2011 1 B8753
PH1/B/11/49243/5/2011 2 B8752
TASCO/B/74/15427/2011 3 B8745
REO/B/103/25174/5/2011 4 B8749
CHEMCO/B/44/36334/2011 5 B8774
select * from bill_file;
(records as follows);
bill_no cr_bill_no bill_dt bill_pend_Amt bill_active
443 1 2011-04-22 50 A
445 2 2011-04-29 190 A
446 3 2011-05-02 300 A
447 4 2011-05-09 450 A
448 5 2011-05-09 500 A
select * from receipt_file;
(records as follows);
rcpt_no bill_no rcpt_dt Amt
512 443 2011-04-22 450
513 445 2011-04-29 600
514 446 2011-05-02 890
516 447 2011-05-09 950
516 448 2011-05-09 1050
From using the four tables and using store procedure i want to get the below output as follows,
how can i below output using store procedure.
stud_id bcr_batch_id cr_bill_no bill_no bill_dt bill_pend_Amt bill_active rcpt_no rcpt_dt Amt
1 B8753 1 443 2011-04-22 50 A 512 2011-04-22 450
[edit]Code block added - OriginalGriff[/edit]
SELECT course_registration.stud_id, batch_course_registration.bcr_batch_id, bill_file.cr_bill_no, receipt_file.bill_no, bill_file.bill_dt, bill_file.bill_pend_Amt, bill_file.bill_active , receipt_file.rcpt_no, receipt_file.rcpt_dt, Amt FROM course_registration cr inner join batch_course_registration bcr on cr.cr_bill_no = crb.cr_bill_no inner join bill_file bf on bf.cr_bill_no = crb.cr_bill_no inner join receipt_file rf on rf.bill_no = bf.bill_no WHERE cr.stud_id = <write here student id>
By the way what was issue while you doing it?
这篇关于通过使用存储过程,使用数据库记录获取输出的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!