我这里有4张表(jcc_ysk(应收款),jcc_sfd(收费单),jcc_jmd(减免单),jcc_tfd(退费单))jcc_ysk字段有:sfqjdm(收费期间代码),xh(学号),sfxmdm(收费项目代码),bmdm(部门代码),zydm(专业代码),bjdm(班级代码),ysje(应缴金额)
jcc_sfd字段有:sfqjdm(收费期间代码),sfxmdm(收费项目代码),xh(学号),je(实缴金额)
jcc_jmd字段有:sfqjdm(收费期间代码),sfxmdm(收费项目代码),xh(学号),je(减免金额)
jcc_tfd字段有:sfqjdm(收费期间代码),sfxmdm(收费项目代码),xh(学号),je(退费金额)问题是:如何生成如下表jcc_sfzz(收费总帐表)
jcc_sfzz字段:sfqjdm,sfxmdm,xh,bmdm,zydm,bjdm,yjje(应缴金额),jmje(减免金额),tfje(退费金额),sjje(实缴金额)这个SQL语句应该怎么写,刚开始想得很简单,后来做起来,越做越复杂,请各位高手帮帮我,谢谢大家了~!
jcc_sfd字段有:sfqjdm(收费期间代码),sfxmdm(收费项目代码),xh(学号),je(实缴金额)
jcc_jmd字段有:sfqjdm(收费期间代码),sfxmdm(收费项目代码),xh(学号),je(减免金额)
jcc_tfd字段有:sfqjdm(收费期间代码),sfxmdm(收费项目代码),xh(学号),je(退费金额)问题是:如何生成如下表jcc_sfzz(收费总帐表)
jcc_sfzz字段:sfqjdm,sfxmdm,xh,bmdm,zydm,bjdm,yjje(应缴金额),jmje(减免金额),tfje(退费金额),sjje(实缴金额)这个SQL语句应该怎么写,刚开始想得很简单,后来做起来,越做越复杂,请各位高手帮帮我,谢谢大家了~!
解决方案 »
- 表和视图的只读权限+所有存储过程的执行权限=?角色
- 有关聚集函数SUM()的问题
- 如何恢复误删除SYSPWHD2的内容
- sql语句错行问题
- 如何用SQL语句把某一值设为NULL?
- 请问非聚集索引有哪些缺点
- 请问我要将本机的SQLServer数据全部导到虚拟主机的SQLServer服务器上应该怎么做?
- 当读取1万条记录以上,应怎么做才有高效率?
- oracle中怎么得到表之间的关系?
- 新人求指教下SQL怎么对比一列的内容是否在另一列中存在并且一致呢???
- 超奇怪的问题,百思不得其解.大家帮忙.EXEC(@Sql) 和把@Sql PRINT出来再执行居然不一样?
- 关于SQLSERVER新增记录时,如何利用消息通知应用程序问题??大虾看进来
SELECT
A.*,
'减免金额'=(SELECT SUM(JE) FROM jcc_sfd WHERE sfqjdm=A.sfqjdm),
'退费金额'=(SELECT SUM(JE) FROM jcc_jmd WHERE sfqjdm=A.sfqjdm),
'实缴金额'=(SELECT SUM(JE) FROM jcc_tfd WHERE sfqjdm=A.sfqjdm)
FROM [jcc_ysk] A
sum(yjje) as yjje,sum(jmje) as jmje,sum(tfje) as tfje,sum(sjje) as sjje
from (
select sfqjdm,sfxmdm,xh,bmdm,zydm,bjdm,yjje,0 as jmje,0 as tfje,0 as sjje
from jcc_ysk
union all
select sfqjdm,sfxmdm,xh,'' as bmdm,'' as zydm,'' as bjdm,0 as yjje,0 as jmje,0 as tfje,je as sjje
from jcc_sfd
union all
select sfqjdm,sfxmdm,xh,'' as bmdm,'' as zydm,'' as bjdm,0 as yjje,je as jmje,0 as tfje,0 as sjje
from jcc_jmd
union all
select sfqjdm,sfxmdm,xh,'' as bmdm,'' as zydm,'' as bjdm,0 as yjje,0 as jmje,je as tfje,0 as sjje
from jcc_tfd
) as t
group by sfqjdm,sfxmdm,xh
下面指的前四张表是(jcc_ysk(应收款),jcc_sfd(收费单),jcc_jmd(减免单),jcc_tfd(退费单))(1),jcc_sfzz(收费总帐)是已经存在的表,前四张表要把它们的数据要做联合查询汇总,再插入到(收费总帐)表的.
(2),jcc_sfzz(收费总帐)表里也可能有前四张表以前做联合查询汇总的数据,在这里也要做一个判断,根据(sfqjdm,sfxmdm,xh)三个字段来select判断收费总帐有没有值.
如果有的话,那就update;否则,insert.不知道我有没有说清楚,十分感谢楼上两位的回答.
分不够在加,谢谢各位了~!