昨天试了你写SQL应该对着呢(我对业务不太熟悉,请允许我这么说),在里非常感谢一下,但是还有一点需要改一下,谢谢。---------------------------------------
1.1 单位信息T_FSBASEUNIT序号 字段名称 数据类型 描述 备注
1 ItemId Number(9) 唯一标识号
2 UnitCode Varchar2(18) 单位(部门)编码
3 UnitName Varchar2(100) 单位(部门)名称
4 IsSector Varchar2(1) 是否部门
5 ParentUnitID Number(9) 上级单位 当为部门时是0 3.1 缴款书详细信息T_FSCHARGEBILL(Bill表) 序号 字段名称 数据类型 描述 备注
1 ItemID Number(9) 流水号
2 BillTypeID Number(9) 票据种类唯一标识号
3 BillCode Varchar2(20) 单据号
4 UnitCode Varchar2(18) 执收单位唯一标识号
5 FillDate Varchar2(12) 填制日期
3.2 缴款书细单T_ FSCHARGEBILLDETAIL(Bill与项目挂勾) 序号 字段名称 数据类型 描述 备注
1 ItemID Number(9) 流水号
2 BillheadID Number (9) 缴款书流水号
3 FromItemID Number (9) 项目编码流水号
4 Fund Number(15,2) 金额
5 Amount Number(9) 数量
1.6 项目信息T_FSITEM 序号 字段名称 数据类型 描述 备注
1 ItemID Number(9) 唯一标识号
2 ItemCode Varchar2(18) 项目编码
3 ItemName Varchar2(100) 项目名称
4 IncomeType Varchar2(4) 收入分类
5 FundClass Varchar2(4) 资金性质 -----------------------------------------------------(你写的语句)
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)
from T_FSBASEUNIT a,T_FSCHARGEBILL 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------------------------------------
再麻烦改一下,在 3.1 缴款书详细信息T_FSCHARGEBILL(Bill表) 里有个FillDate,这个要求是选择了日期范围(开始日期--结束日期)之后然后再实现后面的查询,所以应该是先在 3.1 缴款书详细信息T_FSCHARGEBILL(Bill表) 表里进行日期筛选之后,然后在用上面的语句查询2.就是还有一个部门汇总要求(和原来那个是分开的),上面的不是单位出来了吗?还有就是当选择单位/部门之后(从下拉框里选),单位应该和上面的是一样的.而部门会列出这个部门下的所有单位来,当然相同的项目也得Sum.
项目1
帐单1
项目2
单位1
项目1
帐单2
项目3 (这时候项目1就应该合并,产生三个项目,你上面的语句已经实现了)
部门
项目1
帐单3
项目4
单位2
项目5
帐单2
项目6 (这时候就应该是显示部门编码,部门名称,项目...也就是项目1(三个相加),实现产生6个项目)
非常感谢.一会再开贴..再给100分.
1.1 单位信息T_FSBASEUNIT序号 字段名称 数据类型 描述 备注
1 ItemId Number(9) 唯一标识号
2 UnitCode Varchar2(18) 单位(部门)编码
3 UnitName Varchar2(100) 单位(部门)名称
4 IsSector Varchar2(1) 是否部门
5 ParentUnitID Number(9) 上级单位 当为部门时是0 3.1 缴款书详细信息T_FSCHARGEBILL(Bill表) 序号 字段名称 数据类型 描述 备注
1 ItemID Number(9) 流水号
2 BillTypeID Number(9) 票据种类唯一标识号
3 BillCode Varchar2(20) 单据号
4 UnitCode Varchar2(18) 执收单位唯一标识号
5 FillDate Varchar2(12) 填制日期
3.2 缴款书细单T_ FSCHARGEBILLDETAIL(Bill与项目挂勾) 序号 字段名称 数据类型 描述 备注
1 ItemID Number(9) 流水号
2 BillheadID Number (9) 缴款书流水号
3 FromItemID Number (9) 项目编码流水号
4 Fund Number(15,2) 金额
5 Amount Number(9) 数量
1.6 项目信息T_FSITEM 序号 字段名称 数据类型 描述 备注
1 ItemID Number(9) 唯一标识号
2 ItemCode Varchar2(18) 项目编码
3 ItemName Varchar2(100) 项目名称
4 IncomeType Varchar2(4) 收入分类
5 FundClass Varchar2(4) 资金性质 -----------------------------------------------------(你写的语句)
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)
from T_FSBASEUNIT a,T_FSCHARGEBILL 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------------------------------------
再麻烦改一下,在 3.1 缴款书详细信息T_FSCHARGEBILL(Bill表) 里有个FillDate,这个要求是选择了日期范围(开始日期--结束日期)之后然后再实现后面的查询,所以应该是先在 3.1 缴款书详细信息T_FSCHARGEBILL(Bill表) 表里进行日期筛选之后,然后在用上面的语句查询2.就是还有一个部门汇总要求(和原来那个是分开的),上面的不是单位出来了吗?还有就是当选择单位/部门之后(从下拉框里选),单位应该和上面的是一样的.而部门会列出这个部门下的所有单位来,当然相同的项目也得Sum.
项目1
帐单1
项目2
单位1
项目1
帐单2
项目3 (这时候项目1就应该合并,产生三个项目,你上面的语句已经实现了)
部门
项目1
帐单3
项目4
单位2
项目5
帐单2
项目6 (这时候就应该是显示部门编码,部门名称,项目...也就是项目1(三个相加),实现产生6个项目)
非常感谢.一会再开贴..再给100分.
where a.itemid=b.unitid and b.itemid=c.billheadid and c.fromitemid=d.itemno and b.FillDate>='2007-01-01' and b.FillDate<='2007-12-08'
where a.itemid = b.unitid and b.itemid = c.billheadid and c.fromitemid = d.itemno and FillDate > ='2007-01-01' and FillDate < = '2007-12-08' 错误提示:
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause). 因为:必须先过滤完日期之后再进行后面的查询统计, 项目1
帐单1 日期1
项目2
单位1
项目1
帐单2 日期2
项目3 这种情况下,项目1要合并的,但是分组的话,它不知道用哪个日期了啊.再次感谢
(select 你需要的字段 from T_FSBASEUNIT where 时间...) t
你还是没明白我的意思,如果加时间限制是先从Bill表里选出这个时间限制的帐单来,然后再根据单位找项目去,而不是查询完了之后过滤,两者不一样的啊.
先谢谢了.
那请问第二个问题,按果选择单位/部门的时候怎么办呢?如果选择了单位好办就是加个单位=''如果按部门的话呢,,因为部门也在Unit表里(根据IsSector Varchar2(1) 是否部门 来标识)必须按部门分类了.order by 应该不行吧,因为不知道哪个部门的,不过我想在你第一个基础上修改一下,当选出单位名称的时候,可以连它是不是部门选择出来,然后再order by 可以吗,我不懂..谢谢了..
group by a.UnitName,a.unitcode,d.itemname,d.itemcode with rollup
group by a.UnitName,a.unitcode,d.itemname,d.itemcode with rollup
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现在如果按部门汇总的话,项目1必须加上去的..我试着用 group by substr(a.UnitCode,1,3)可是不行.注:前三位是部门编码,后三位是单位.谢谢..
XX财政局 项目2
XX财政局 项目1
交通局 项目4 说错了,应该是这样的.你看一下规则,谢谢了.
如: select col1 , '合计' col2, sum(col3) col3 from ... group by col1
谢谢了..
我需要替换成部门的.
如: select col1 , '合计' col2, sum(col3) col3 from ... group by col1
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
我这么写的,好像不对.
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 and a.issector = 0
group by a.unitname,a.unitcode,d.itemname,d.itemcode
order by a.UnitName,a.unitcode,d.itemname,d.itemcode
不管出于什么目的,现在好人不多了so as to 让我这个向来只看贴不回帖的 也冲动的 顶一把