主表:tbM
sfid(char18), //身份证号
address(char) //户籍地
业务表:
表1、
tbMzjz //门诊救助
sfid,
je, //金额
Jzsj//救助时间
表2、
tbZyzj //住院救助
sfid,
je,
Jzsj表3、
tbTempJz //临时救助
sfid;
je,
jzsj现在想统计:2010年1月-6月救助实施汇总表户籍地 门诊救助 住院救助 临时救助
--------------------------------------------------------
人数|金额 人数|金额 人数|金额
--------------------------------------------------------
良田镇 10 | 33300.22 22 |45622.56 32| 65801.29
桥口镇 11 | 43210.88 11 |21239.33 18| 32010.77
.................
sfid(char18), //身份证号
address(char) //户籍地
业务表:
表1、
tbMzjz //门诊救助
sfid,
je, //金额
Jzsj//救助时间
表2、
tbZyzj //住院救助
sfid,
je,
Jzsj表3、
tbTempJz //临时救助
sfid;
je,
jzsj现在想统计:2010年1月-6月救助实施汇总表户籍地 门诊救助 住院救助 临时救助
--------------------------------------------------------
人数|金额 人数|金额 人数|金额
--------------------------------------------------------
良田镇 10 | 33300.22 22 |45622.56 32| 65801.29
桥口镇 11 | 43210.88 11 |21239.33 18| 32010.77
.................
的数据后 行转列
sum(t2.je) je2,count(t2.sfid) c2,
sum(t3.je) je3,count(t3.sfid) c3
from tbm t, 表1 t1,表2 t2, 表3 t3
where t1.jzsj(+) between date1 and date2
and t2.jzsj(+) between date1 and date2
and t3.jzsj(+) between date1 and date2
and t.sfid = t1.sfid(+)
and t.sfid = t2.sfid(+)
and t.sfid = t3.sfid(+)
group by t.address
FROM
(SELECT address,COUNT(*) RS FROM tbM GROUP BY address) T1,
(SELECT b.address,count(*) RS,sum(a.je) JinE FROM tbMzjz a,tbM b WHERE a.sfid = b.sfid GROUP BY b.address) T2,
(SELECT b.address,count(*) RS,sum(a.je) JinE FROM tbZyjz a,tbM b WHERE a.sfid = b.sfid GROUP BY b.address) T3,
(SELECT b.address,count(*) RS,sum(a.je) JinE FROM tbTempjz a,tbM b WHERE a.sfid = b.sfid GROUP BY b.address) T4
WHERE T1.address = T2.address AND T1.address = T3.address AND T1.address = T4.address
select address,count(b.spid)||'|'||sum(b.je) "门诊救助人数|金额",
count(c.spid)||' |'||sum(c.je) "住院救助人数|金额",
count(d.spid)||' |'||sum(d.je) "临时救助人数|金额"
from tbm a,tb1 b,tb2 c,tb3 d
where a.spid=b.spid and a.spid=c.spid and a.spid=d.spid
group by address--决定你的身份证的号码跟户籍应该还要处理下
select * from (select tbM.SFID, count(tbMzjz.JE),sum(tbMzjz.JE),count(tbZyzj.JE),sum(tbZyzj.JE),count(tbTempJz.JE),sum(tbTempJz.JE) from tbM
left join tbMzjz on tbM.SFID = tbMzjz.SFID
left join tbZyzj on tbM.SFID = tbZyzj.SFID
left join tbTempJz on tbM.SFID = tbTempJz.SFID
group by tbM.sfid order by tbM.SFID) tab, tbM where tab.sfid = tbM.SFID
----------------------------------------------------------------------------
1 1 100 1 1000 1 yyq_address1
2 1 200 1 2000 2 yyq_address2
3 1 300 0 3 yyq_address3
4 1 400 0 4 yyq_address4
5 1 500 0 5 yyq_address5
主表1条记录,从表1有3条,丛表2有3条,直接关联会产生9条记录,然后无论是求和还是求数量都会出错。
from tbM
left join
(select address,sum(jzje) as ZyjzSum,count(tbZyjz.sfid) as ZyjzCount from tbZyjz
left join tbM on tbM.sfid=tbzyjz.sfid
where jzsj between '2010-1-1' and '2010-12-31'
group by address)a on a.address=tbM.addressleft join
(select address,sum(jzje) as MzjzSum,count(tbMzjz.sfid) as MzjzCount from tbMzjz
left join tbM on tbM.sfid=tbmzjz.sfid
where jzsj between '2010-1-1' and '2010-12-31'
group by address)b on b.address=tbM.addressleft join
(select address,sum(jzje) as TempSum,count(tbtempjz.sfid) as TempCount from tbTempjz
left join tbM on tbM.sfid=tbtempjz.sfid
where jzsj between '2010-1-1' and '2010-12-31'
group by address)c on c.address=tbM.addressleft join
(select address,sum(jzje) as ChcbSum,count(tbChcb.sfid) as ChcbCount from tbChcb
left join tbM on tbM.sfid=tbChcb.sfid
where jzsj between '2010-1-1' and '2010-12-31'
group by address)d on d.address=tbM.address