select termyear, min(uni_no) uni_no, min(name) name, fcode, startyear, sum(samt) samt, sum(yamt) yamt, sum(tamt) tamt,
sum(jamt) jamt, sum(amt) amt from
(select 1900 termyear,0 dist,'学号' uni_no,'姓名' name, '费用项' fcode,190001 startyear,0.0 samt,0.0 yamt, 0.0 tamt,
0.0 jamt,0.0 amt from dual where 1=2union select max(termYear) termyear, max(2002-termyear), uni_no, max(name) name, fcode, startyear,
sum(samt) samt, sum(yamt) yamt,sum(tamt) tamt,sum(jamt)
jamt,sum(samt-yamt+tamt-jamt) amt from fei_detail2002
where uni_no = '08051087'
group by uni_no,startYear,fcode union select max(termYear) termyear, max(2003-termyear), uni_no, max(name) name, fcode, startyear, sum(samt) samt, sum(yamt) yamt
,sum(tamt) tamt,sum(jamt) jamt,sum(samt-yamt+tamt-jamt) amt
from fei_detail2003 where uni_no = '08051087' group by uni_no,startYear,fcode union select max(termYear) termyear, max(2004-termyear), uni_no, max(name) name, fcode, startyear, sum(samt) samt, sum(yamt) yamt,
sum(tamt) tamt,sum(jamt) jamt,sum(samt-yamt+tamt-jamt) amt from fei_detail2004
where uni_no = '08051087' group by uni_no,startYear,fcode union select max(termYear) termyear, max(2005-termyear), uni_no, max(name) name, fcode,
startyear, sum(samt) samt, sum(yamt) yamt,sum(tamt) tamt,sum(jamt) jamt,sum(samt-yamt+tamt-jamt) amt
from fei_detail2005 where uni_no = '08051087'
group by uni_no,startYear,fcodeunion select max(termYear) termyear, max(2006-termyear), uni_no, max(name) name, fcode, startyear, sum(samt) samt,
sum(yamt) yamt,sum(tamt) tamt,sum(jamt) jamt,sum(samt-yamt+tamt-jamt) amt
from fei_detail2006 where uni_no = '08051087' group by uni_no,startYear,fcodeunion select max(termYear) termyear, max(2007-termyear), uni_no, max(name) name, fcode, startyear, sum(samt) samt, sum(yamt) yamt,
sum(tamt) tamt,sum(jamt) jamt,sum(samt-yamt+tamt-jamt) amt from fei_detail2007 where uni_no = '08051087'
group by uni_no,startYear,fcode union select max(termYear) termyear, max(2008-termyear), uni_no, max(name) name, fcode, startyear, sum(samt) samt, sum(yamt) yamt,
sum(tamt) tamt,sum(jamt) jamt,sum(samt-yamt+tamt-jamt) amt
from fei_detail2008 where uni_no = '08051087' group by uni_no,startYear,fcode union select max(termYear) termyear, max(2009-termyear), uni_no, max(name) name, fcode, startyear, sum(samt) samt, sum(yamt) yamt,
sum(tamt) tamt,sum(jamt) jamt,sum(samt-yamt+tamt-jamt) amt from fei_detail2009 where uni_no = '08051087'
group by uni_no,startYear,fcode )t group by termyear, fcode, startyear order by termyear, fcode, startyear 该段SQL语句为某某学生收费系统的查看学号为08051087在校所有学年费用的情况(查询显示:学年,费用项,开账年月,学号,姓名,应收,已收,退款,减免,余额),由于公司不愿提供数据结构说明,所以这段代码我也是跟踪出来的,但是感觉还是读不懂,请高手不吝赐教。
sum(jamt) jamt, sum(amt) amt from
(select 1900 termyear,0 dist,'学号' uni_no,'姓名' name, '费用项' fcode,190001 startyear,0.0 samt,0.0 yamt, 0.0 tamt,
0.0 jamt,0.0 amt from dual where 1=2union select max(termYear) termyear, max(2002-termyear), uni_no, max(name) name, fcode, startyear,
sum(samt) samt, sum(yamt) yamt,sum(tamt) tamt,sum(jamt)
jamt,sum(samt-yamt+tamt-jamt) amt from fei_detail2002
where uni_no = '08051087'
group by uni_no,startYear,fcode union select max(termYear) termyear, max(2003-termyear), uni_no, max(name) name, fcode, startyear, sum(samt) samt, sum(yamt) yamt
,sum(tamt) tamt,sum(jamt) jamt,sum(samt-yamt+tamt-jamt) amt
from fei_detail2003 where uni_no = '08051087' group by uni_no,startYear,fcode union select max(termYear) termyear, max(2004-termyear), uni_no, max(name) name, fcode, startyear, sum(samt) samt, sum(yamt) yamt,
sum(tamt) tamt,sum(jamt) jamt,sum(samt-yamt+tamt-jamt) amt from fei_detail2004
where uni_no = '08051087' group by uni_no,startYear,fcode union select max(termYear) termyear, max(2005-termyear), uni_no, max(name) name, fcode,
startyear, sum(samt) samt, sum(yamt) yamt,sum(tamt) tamt,sum(jamt) jamt,sum(samt-yamt+tamt-jamt) amt
from fei_detail2005 where uni_no = '08051087'
group by uni_no,startYear,fcodeunion select max(termYear) termyear, max(2006-termyear), uni_no, max(name) name, fcode, startyear, sum(samt) samt,
sum(yamt) yamt,sum(tamt) tamt,sum(jamt) jamt,sum(samt-yamt+tamt-jamt) amt
from fei_detail2006 where uni_no = '08051087' group by uni_no,startYear,fcodeunion select max(termYear) termyear, max(2007-termyear), uni_no, max(name) name, fcode, startyear, sum(samt) samt, sum(yamt) yamt,
sum(tamt) tamt,sum(jamt) jamt,sum(samt-yamt+tamt-jamt) amt from fei_detail2007 where uni_no = '08051087'
group by uni_no,startYear,fcode union select max(termYear) termyear, max(2008-termyear), uni_no, max(name) name, fcode, startyear, sum(samt) samt, sum(yamt) yamt,
sum(tamt) tamt,sum(jamt) jamt,sum(samt-yamt+tamt-jamt) amt
from fei_detail2008 where uni_no = '08051087' group by uni_no,startYear,fcode union select max(termYear) termyear, max(2009-termyear), uni_no, max(name) name, fcode, startyear, sum(samt) samt, sum(yamt) yamt,
sum(tamt) tamt,sum(jamt) jamt,sum(samt-yamt+tamt-jamt) amt from fei_detail2009 where uni_no = '08051087'
group by uni_no,startYear,fcode )t group by termyear, fcode, startyear order by termyear, fcode, startyear 该段SQL语句为某某学生收费系统的查看学号为08051087在校所有学年费用的情况(查询显示:学年,费用项,开账年月,学号,姓名,应收,已收,退款,减免,余额),由于公司不愿提供数据结构说明,所以这段代码我也是跟踪出来的,但是感觉还是读不懂,请高手不吝赐教。
min(uni_no) uni_no, --学号
min(name) name, --姓名
fcode, --费用项
startyear, --开帐年月
sum(samt) samt, --应收
sum(yamt) yamt, --已收
sum(tamt) tamt, --退款
sum(jamt) jamt, --减免
sum(amt) amt --余额 from (select 1900 termyear,
0 dist,
'学号' uni_no,
'姓名' name,
'费用项' fcode,
190001 startyear,
0.0 samt,
0.0 yamt,
0.0 tamt,
0.0 jamt,
0.0 amt
from dual
where 1 = 2 --这个select用来显示一个表头第一行
union --合并起来
select max(termYear) termyear, --学年
max(2002 - termyear) , --计算2002年
uni_no, --学号
max(name) name, --姓名
fcode, --费用项
startyear, --开帐年月
sum(samt) samt, --应收
sum(yamt) yamt, --已收
sum(tamt) tamt, --退款
sum(jamt) jamt, --减免
sum(samt - yamt + tamt - jamt) amt --余额
from fei_detail2002 --2002年明细表
where uni_no = '08051087'
group by uni_no, startYear, fcode --按学号,开帐年月,费用项,分组
....--中间省略了2003-2008年的union,其实是一样的语句
union --合并起来
select max(termYear) termyear, --计算2009年
max(2009 - termyear),
uni_no,
max(name) name,
fcode,
startyear,
sum(samt) samt,
sum(yamt) yamt,
sum(tamt) tamt,
sum(jamt) jamt,
sum(samt - yamt + tamt - jamt) amt
from fei_detail2009 --2009年明细表
where uni_no = '08051087'
group by uni_no, startYear, fcode) t
group by termyear, fcode, startyear --分组
order by termyear, fcode, startyear --排序
不胜感激!
select 1900 termyear,
0 dist,
'学号' uni_no,
'姓名' name,
'费用项' fcode,
190001 startyear,
0.0 samt,
0.0 yamt,
0.0 tamt,
0.0 jamt,
0.0 amt
from dual
where 1 = 2
只是为了生成UNION后总表的字段名
TERMYEAR DIST UNI_NO NAME FCODE STARTYEAR SAMT YAMT TAMT JAMT AMT