select a.UnitName,a.UnitCode,d.itemname,d.itemcode,
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '政府性基金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '政府性基金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '专项收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '专项收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '彩票资金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '彩票资金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '行政事业性收费收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '行政事业性收费收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '罚没收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '罚没收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '国有资本经营性收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '国有资本经营性收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '国有资源(资产)有偿使用收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '国有资源(资产)有偿使用收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '其他收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '其他收入' then c.fund else 0 end),
sum(c.fund)
from T_FSBASEUNIT a,(select itemid, billtypeid, billcode, unitid, filldate from t_fschargebill where substr(filldate,1,10)>='2007-01-01' and substr(filldate,1,10)<='2007-02-01') b ,
T_FSCHARGEBILLDETAIL c,T_FSITEM d
where a.itemid = b.unitid and b.itemid = c.billheadid and c.fromitemid = d.itemno
group by a.unitname,a.unitcode,d.itemname,d.itemcode
order by a.UnitName,a.unitcode,d.itemname,d.itemcodeunion allselect a.UnitName,a.UnitCode,d.itemname,d.itemcode,
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '政府性基金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '政府性基金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '专项收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '专项收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '彩票资金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '彩票资金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '行政事业性收费收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '行政事业性收费收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '罚没收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '罚没收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '国有资本经营性收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '国有资本经营性收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '国有资源(资产)有偿使用收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '国有资源(资产)有偿使用收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '其他收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '其他收入' then c.fund else 0 end),
'合计'
from T_FSBASEUNIT a,(select itemid, billtypeid, billcode, unitid, filldate from t_fschargebill where substr(filldate,1,10)>='2007-01-01' and substr(filldate,1,10)<='2007-02-01') b ,
T_FSCHARGEBILLDETAIL c,T_FSITEM d
where a.itemid = b.unitid and b.itemid = c.billheadid and c.fromitemid = d.itemno
group by a.unitname,a.unitcode,d.itemname,d.itemcode
order by a.UnitName,a.unitcode,d.itemname,d.itemcode
我是这样写的,肯定不行啦,还有就是我最后显示的应该是部门了不是单位了。部门1 项目1
项目2
项目3
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '政府性基金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '政府性基金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '专项收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '专项收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '彩票资金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '彩票资金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '行政事业性收费收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '行政事业性收费收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '罚没收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '罚没收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '国有资本经营性收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '国有资本经营性收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '国有资源(资产)有偿使用收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '国有资源(资产)有偿使用收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '其他收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '其他收入' then c.fund else 0 end),
sum(c.fund)
from T_FSBASEUNIT a,(select itemid, billtypeid, billcode, unitid, filldate from t_fschargebill where substr(filldate,1,10)>='2007-01-01' and substr(filldate,1,10)<='2007-02-01') b ,
T_FSCHARGEBILLDETAIL c,T_FSITEM d
where a.itemid = b.unitid and b.itemid = c.billheadid and c.fromitemid = d.itemno
group by a.unitname,a.unitcode,d.itemname,d.itemcode
order by a.UnitName,a.unitcode,d.itemname,d.itemcodeunion allselect a.UnitName,a.UnitCode,d.itemname,d.itemcode,
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '政府性基金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '政府性基金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '专项收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '专项收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '彩票资金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '彩票资金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '行政事业性收费收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '行政事业性收费收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '罚没收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '罚没收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '国有资本经营性收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '国有资本经营性收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '国有资源(资产)有偿使用收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '国有资源(资产)有偿使用收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '其他收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '其他收入' then c.fund else 0 end),
'合计'
from T_FSBASEUNIT a,(select itemid, billtypeid, billcode, unitid, filldate from t_fschargebill where substr(filldate,1,10)>='2007-01-01' and substr(filldate,1,10)<='2007-02-01') b ,
T_FSCHARGEBILLDETAIL c,T_FSITEM d
where a.itemid = b.unitid and b.itemid = c.billheadid and c.fromitemid = d.itemno
group by a.unitname,a.unitcode,d.itemname,d.itemcode
order by a.UnitName,a.unitcode,d.itemname,d.itemcode
我是这样写的,肯定不行啦,还有就是我最后显示的应该是部门了不是单位了。部门1 项目1
项目2
项目3
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '政府性基金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '政府性基金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '专项收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '专项收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '彩票资金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '彩票资金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '行政事业性收费收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '行政事业性收费收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '罚没收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '罚没收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '国有资本经营性收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '国有资本经营性收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '国有资源(资产)有偿使用收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '国有资源(资产)有偿使用收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '其他收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '其他收入' then c.fund else 0 end),
sum(c.fund)
from T_FSBASEUNIT a,(select itemid, billtypeid, billcode, unitid, filldate from t_fschargebill where substr(filldate,1,10)> ='2007-01-01' and substr(filldate,1,10) <='2007-02-01') b ,
T_FSCHARGEBILLDETAIL c,T_FSITEM d
where a.itemid = b.unitid and b.itemid = c.billheadid and c.fromitemid = d.itemno and a.issector = 0
group by a.unitname,a.unitcode,d.itemname,d.itemcode
order by a.UnitName,a.unitcode,d.itemname,d.itemcode
--或者你不要a.unitcode
select a.UnitName,d.itemname,d.itemcode,
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '政府性基金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '政府性基金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '专项收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '专项收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '彩票资金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '彩票资金收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '行政事业性收费收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '行政事业性收费收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '罚没收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '罚没收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '国有资本经营性收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '国有资本经营性收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '国有资源(资产)有偿使用收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '国有资源(资产)有偿使用收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入国库' and d.IncomeType = '其他收入' then c.fund else 0 end),
sum(case when d.FundClass = '缴入专户' and d.IncomeType = '其他收入' then c.fund else 0 end),
sum(c.fund)
from T_FSBASEUNIT a,(select itemid, billtypeid, billcode, unitid, filldate from t_fschargebill where substr(filldate,1,10)> ='2007-01-01' and substr(filldate,1,10) <='2007-02-01') b ,
T_FSCHARGEBILLDETAIL c,T_FSITEM d
where a.itemid = b.unitid and b.itemid = c.billheadid and c.fromitemid = d.itemno and a.issector = 0
group by a.unitname,d.itemname,d.itemcode
order by a.UnitName,d.itemname,d.itemcode
1 财厅 100 1
2 XX财政局 100100 0
3 XX财政局 100101 0
4 XX财政局 100102 0
5 交通厅 200 1
6 交通局 200100 0 原来的语句出来的是: XX财政局 项目1
XX财政局 项目2
XX财政局 项目3
交通局 项目4 加上issector=0不行?