数据库小计问题
select FItemID=case when FName is null then '小计' else FItemID end ,FName,t1.FDate ,
FBillNo,FUnit,FQCAux,FQCPrice ,FQCAmount,
FInAux ,FInPrice ,FInAmount,
FOutAux ,FOutPrice,FOutAmount,FQMAux ,FQMPrice ,FQMAmount
from
(select FItemID,FName,FDate,FBillNo,FUnit,sum(FQCAux),FQCPrice,sum(FQCAmount),sum(FInAux),FInPrice,sum(FInAmount),sum(FOutAux),FOutPrice,sum(FOutAmount),sum(FQMAux),FQMPrice,sum(FQMAmount)
from #UFOJYB
group by FItemID,FName,FDate,FBillNo,FUnit,FQCPrice,FInPrice,FOutPrice,FQMPrice
with rollup
) t1
where FItemID is not null and FName is not null
order by FItemID
--union all
--select '合计','','','','','','','','','','','','','','','',''
--from #UFOJYB
问题点数：50、回复次数：8Top
1 楼kugoo2338903（咫尺天涯，网罗人生）回复于 2005-06-03 10:42:08 得分 0
运行提示没有指定列Top
2 楼zjcxc（邹建）回复于 2005-06-03 10:48:03 得分 50
select FItemID=case when FName is null then '小计' else FItemID end ,
FName,
t1.FDate ,
FBillNo,
FUnit,
FQCAux,
FQCPrice ,
FQCAmount,
FInAux ,FInPrice ,FInAmount,
FOutAux ,FOutPrice,FOutAmount,FQMAux ,FQMPrice ,FQMAmount
from (
select FItemID,FName,FDate,FBillNo,FUnit,
sum(FQCAux) FQCAux,
FQCPrice,
sum(FQCAmount) FQCAmount,
sum(FInAux) FInAux,
FInPrice,
sum(FInAmount) FInAmount,
sum(FOutAux) FOutAux,
FOutPrice,
sum(FOutAmount) FOutAmount,
sum(FQMAux) FQMAux,
FQMPrice,
sum(FQMAmount) FQMAmount
from #UFOJYB
group by FItemID,FName,FDate,FBillNo,FUnit,FQCPrice,FInPrice,FOutPrice,FQMPrice
with rollup
) t1
where FItemID is not null and FName is not null
order by FItemID
--union all
--select '合计','','','','','','','','','','','','','','','',''
--from #UFOJYB
Top
3 楼kugoo2338903（咫尺天涯，网罗人生）回复于 2005-06-03 10:53:06 得分 0
邹兄，我试了一下，发现没有小计了，而且数据有很多重复的记录Top
4 楼kugoo2338903（咫尺天涯，网罗人生）回复于 2005-06-03 10:56:45 得分 0
--select * from icstockbill where FTranType='10' or FTranType='29'
create table #JYB (
FItemID varchar (20),
FName varchar (150),
FDate datetime,
FBillNo varchar (100),
FUnit varchar (50),
--FQCAux integer default(0),
--FQCPrice real default(0),
--FQCAmount real default(0),
---------------期初
FInAux integer default(0),
FInPrice real default(0),
FInAmount real default(0),--SONY PCG-R505GL
---------------本期收入
FOutAux integer default(0),
FOutPrice real default(0),
FOutAmount real default(0)
---------------本期发出
)
------------------------------------------------------
create table #UFOJYB (
FItemID varchar (20),
FName varchar (150),
FDate datetime,
FBillNo varchar (100),
FUnit varchar (50),
FQCAux integer default(0),
FQCPrice real default(0),
FQCAmount real default(0),
---------------期初
FInAux integer default(0),
FInPrice real default(0),
FInAmount real default(0),--SONY PCG-R505GL
---------------本期收入
FOutAux integer default(0),
FOutPrice real default(0),
FOutAmount real default(0),
---------------本期发出
FQMAux integer default(0),
FQMPrice real default(0),
FQMAmount real default(0)
---------------期末
)
-----------------------------------------------------
create table #QC (
FItemID varchar (20),
FQCAux integer ,
FQCPrice real default(0),
FQCAmount real default(0)
)
----------------------物料的期初余额-----------------------
insert into #QC (FItemID, FQCAux, FQCPrice, FQCAmount)
Select t1.Fnumber As FItemID ,Sum(u1.FBegQty) As FBegQty,
(Case Sum(u1.FBegQty) When 0 Then 0 Else (Sum(u1.FBegBal)-Sum(u1.FBegDiff))/Sum(u1.FBegQty) End) As FBegPrice,
Sum(u1.FBegBal) As FBegAmount From ICInvBal u1
Inner Join t_ICItem t1 On u1.FItemID=t1.FItemID
Left Join t_Stock t2 On u1.FStockID=t2.FItemID
Left Join t_StockPlace t11 On u1.FStockPlaceID=t11.FSPID
Where u1.FYear=2004 And u1.FPeriod=1 --会计期间
And Not (u1.FBegQty = 0 And u1.FBegBal = 0)
Group By t1.Fnumber Order By t1.Fnumber
----------------------物料的期初余额-----------------------
----------------------本期收入-----------------------
insert into #JYB(FItemID, FName,FDate, FBillNo, FUnit, FInAux, FInPrice, FInAmount)
select t3.FNumber as 物料代码,t3.FName as 物料名称,t1.FDate as 日期,
t1.FBillNo as 单据号,t4.FName as 单位,
t2.FAuxPrice as 收入数量,t2.FPrice as 收入单价,t2.FAmount as 收入金额
from icstockbill t1
Left Join ICStockBillEntry t2 on t1.FInterID=t2.FInterID--出入库单据分录表
Left Join T_ICItem t3 on t2.FItemID=t3.FItemID--物料表
Left Join T_MeasureUnit t4 on t3.FUnitGroupID=t4.FUnitGroupID--计量单位表
where t1.FTranType='10'
----------------------本期收入-----------------------
----------------------本期发出-----------------------
insert into #JYB(FItemID, FName,FDate, FBillNo, FUnit, FOutAux, FOutPrice, FOutAmount)
select t3.FNumber as 物料代码,t3.FName as 物料名称,t1.FDate as 日期,
t1.FBillNo as 单据号,t4.FName as 单位,
t2.FAuxPrice as 发出数量,t2.FPrice as 发出单价,t2.FAmount as 发出金额
from icstockbill t1
Left Join ICStockBillEntry t2 on t1.FInterID=t2.FInterID--出入库单据分录表
Left Join T_ICItem t3 on t2.FItemID=t3.FItemID--物料表
Left Join T_MeasureUnit t4 on t3.FUnitGroupID=t4.FUnitGroupID--计量单位表
where t1.FTranType='29'
----------------------本期发出-----------------------
----------------------物料的期初余额-----------------------
insert into #UFOJYB
select t1.FItemID as 物料代码,t1.FName as 物料名称,t1.FDate 日期,
t1.FBillNo as 单据号码,t1.FUnit as 单位,t2.FQCAux as 期初数量,
t2.FQCPrice as 期初单价,t2.FQCAmount as 期初金额,
t1.FInAux as 本期收入数量,t1.FInPrice as 本期收入单价,t1.FInAmount as 本期收入金额,
t1.FOutAux as 本期发出数量,t1.FOutPrice as 本期发出单价,t1.FOutAmount as 本期发出金额,
isnull(t2.FQCAux,0) + isnull(t1.FInAux,0) - isnull(t1.FOutAux,0) as 期末数量,
(isnull(t2.FQCAmount,0) + isnull(t1.FInAmount,0) - isnull(t1.FOutAmount,0)) / (isnull(t2.FQCAux,0) + isnull(t1.FInAux,0) - isnull(t1.FOutAux,0)) as 期末单价,
isnull(t2.FQCAmount,0) + isnull(t1.FInAmount,0) - isnull(t1.FOutAmount,0) as 期末金额
from #JYB t1
INNER JOIN #QC t2
ON t2.FItemID = t1.FItemID
order by t1.FItemID, t1.FDate
--group by WITH CUBE
--COMPUTE SUM(FInAux), SUM(FOutAux) BY t1.FItemID
----------------------物料的期初余额-----------------------
/*
select FItemID=case when FName is null then '小计' else FItemID end ,FName as 物料名称,t1.FDate 日期,
FBillNo as 单据号码,FUnit as 单位,FQCAux as 期初数量,
FQCPrice as 期初单价,FQCAmount as 期初金额,
FInAux as 本期收入数量,FInPrice as 本期收入单价,FInAmount as 本期收入金额,
FOutAux as 本期发出数量,FOutPrice as 本期发出单价,FOutAmount as 本期发出金额,
FQMAux 期末数量,FQMPrice as 期末单价,FQMAmount 期末金额
*/
select FItemID=case when FName is null then '小计' else FItemID end ,FName,t1.FDate ,
FBillNo,FUnit,FQCAux,FQCPrice ,FQCAmount,
FInAux ,FInPrice ,FInAmount,
FOutAux ,FOutPrice,FOutAmount,FQMAux ,FQMPrice ,FQMAmount
from
(select FItemID,FName,FDate,FBillNo,FUnit,sum(FQCAux),FQCPrice,sum(FQCAmount),sum(FInAux),FInPrice,sum(FInAmount),sum(FOutAux),FOutPrice,sum(FOutAmount),sum(FQMAux),FQMPrice,sum(FQMAmount)
from #UFOJYB
group by FItemID,FName,FDate,FBillNo,FUnit,FQCPrice,FInPrice,FOutPrice,FQMPrice
with rollup
) t1
where FItemID is not null and FName is not null
--union all
--select '合计','','','','','','','','','','','','','','','',''
--from #UFOJYB
order by FItemID
--select * from #ufojyb
drop table #QC
drop table #JYB
drop table #UFOJYB
这是我的存储过程Top
5 楼zjcxc（邹建）回复于 2005-06-03 11:58:47 得分 0
举数据说明你的要求.Top
6 楼kugoo2338903（咫尺天涯，网罗人生）回复于 2005-06-03 12:24:25 得分 0
编号 名称 日期 单据号 期初数量 本期收入数量 本期发出数量 期末数量
001 电脑主机(海蓝) 2004-01-15 001 5 1988 0 1993
001 电脑主机(海蓝) 2004-01-20 002 5 0 100 1893
小计 5 1988 100 1893
002 电脑主机(黑色) 2004-01-15 001 7 1988 0 1995
小计 7 1988 0 1995
Top
7 楼kugoo2338903（咫尺天涯，网罗人生）回复于 2005-06-03 12:26:29 得分 0
编号 名称 日期 单据号 期初数量 本期收入数量 本期发出数量 期末数量
001 电脑主机(海蓝)2004-01-15 001 5 1988 0 1993
001 电脑主机(海蓝)2004-01-20 002 5 0 100 1893
小计 5 1988 100 1893
002 电脑主机(黑色)2004-01-15 001 7 1988 0 1995
小计 7 1988 0 1995Top
8 楼kugoo2338903（咫尺天涯，网罗人生）回复于 2005-06-03 13:57:33 得分 0
邹大侠，在吗？Top
