给你一段例子吧:(写不好,不要见笑)<br>CREATE procedure kcStatistic<br> @d1 varchar(100),<br> @d2 varchar(100),<br> @clid varchar(20)<br>as<br>Create table #tmp(a1 varchar(20),a2 varchar(20),a3 varchar(20),a4 numeric,a5 numeric,a6 numeric,a7 numeric,a8 numeric,a9 numeric,a10 numeric,a11 numeric,a12 numeric,a13 int IDENTITY (1, 1) NOT NULL , )<br><br>Declare Cr_tmp cursor For <br> select clid from consumblelist<br>Open Cr_tmp <br>Fetch Next From Cr_tmp into @clid <br>while (@@fetch_status = 0)<br>begin<br> <br> Insert into #tmp<br> select a.clid as 商品编码,d.clname as 商品名称,e.cksname as 种类,0,0,e.kcnum as 本期份数,e.upperlimit as 最高库存,e.lowerlimit as 最低库存,a.num+c.num as 出货数,isnull(((a.num+c.num)*e.trueprice),0) as 出货成本,isnull((d.fnum1+b.allprice),0) as 出货额,(isnull((d.fnum1+b.allprice),0)-isnull(((a.num+c.num)*e.trueprice),0)) as 毛利润 from <br>(select clid,clname,isnull(sum(fnum1),0)as fnum1 from OutgoodTableback where checkid=1 and (createdate between @d1 and @d2) group by clid,clname ) as d,<br>(select clid,isnull(sum(num),0)as num from OutgoodTableback where checkid=1 and (createdate between @d1 and @d2) group by clid)as a,<br><br>(select a.clid as clid,a.clname,isnull(sum(a.allprice*case cudconsumekind when '消费' then 1 when '退货' then -1 else 0 end ),0) as allprice from compartmentusedetail as a,compartmentuse as b where a.cuid=b.cuid and a.ifprint=1 and b.cuifnot<>2 and b.cuifnot<>3<br>and (cuddate between @d1 and @d2) group by a.clid,a.clname )as b,<br><br>(select a.clid as clid,isnull(sum(a.num*case cudconsumekind when '消费' then 1 when '赠送' then 1 when '配送' then 1 when '退货' then -1 else 0 end ),0) as num from compartmentusedetail as a,compartmentuse as b where a.cuid=b.cuid and a.ifprint=1 and b.cuifnot<>2 and b.cuifnot<>3<br>and (cuddate between @d1 and @d2) group by a.clid) as c,<br>consumblelist as e<br> <br>where a.clid=@clid and a.clid=b.clid and b.clid=c.clid and a.clid=d.clid and a.clid=e.clid --order by a.clid <br>--------------------------------------------------------------------------<br>union<br>select d.clid as 商品编码,d.clname as 商品名称,e.cksname as 种类,0,0,e.kcnum as 本期份数,e.upperlimit as 最高库存,e.lowerlimit as 最低库存,d.num as 出货数,isnull((d.num*e.trueprice),0) as 出货成本,isnull(d.fnum1,0) as 出货额,(isnull(d.fnum1,0)-isnull((d.num*e.trueprice),0)) as 毛利润 from <br>(select clid,clname,isnull(sum(fnum1),0)as fnum1,isnull(sum(num),0)as num from OutgoodTableback where checkid=1 and (createdate between @d1 and @d2) group by clid,clname ) as d <br>,consumblelist as e<br>where d.clid=@clid and d.clid=e.clid and d.clid not in (select a.clid as clid from compartmentusedetail as a,compartmentuse as b where a.cuid=b.cuid and a.ifprint=1 and b.cuifnot<>2 and b.cuifnot<>3<br>and (cuddate between @d1 and @d2) group by a.clid ) <br><br>------------------------------------------------------------------------------------------------<br>union<br>select b.clid as 商品编码,b.clname as 商品名称,e.cksname as 种类,0,0,e.kcnum as 本期份数,e.upperlimit as 最高库存,e.lowerlimit as 最低库存,b.num as 出货数,isnull((b.num*e.trueprice),0) as 出货成本,isnull(b.allprice,0) as 出货额,(isnull(b.allprice,0)-isnull((b.num*e.trueprice),0)) as 毛利润 from <br><br>(select a.clid as clid,a.clname,isnull(sum(a.allprice*case cudconsumekind when '消费' then 1 when '退货' then -1 else 0 end ),0) as allprice<br>,isnull(sum(a.num*case cudconsumekind when '消费' then 1 when '赠送' then 1 when '配送' then 1 when '退货' then -1 else 0 end ),0) as num from compartmentusedetail as a,compartmentuse as b where a.cuid=b.cuid and a.ifprint=1 and b.cuifnot<>2 and b.cuifnot<>3<br>and (cuddate between @d1 and @d2) group by a.clid,a.clname )as b<br>,consumblelist as e<br>where b.clid=@clid and b.clid=e.clid and b.clid not in (select clid from OutgoodTableback where checkid=1 and (createdate between @d1 and @d2) group by clid)<br><br> <br> <br> update #tmp set a4=<br> (select sum(a.num) as num from inproducts as a,intable as b,consumblelist as c<br> where a.clid=@clid and a.clid=c.clid and a.intaid=b.intaid and b.intaisin=1 and (a.createdate between @d1 and @d2)<br> group by a.clid,a.clname),<br> a5=(select sum(a.fnum5) as fnum5 from inproducts as a,intable as b,consumblelist as c<br> where a.clid=@clid and a.clid=c.clid and a.intaid=b.intaid and b.intaisin=1 and (a.createdate between @d1 and @d2)<br> group by a.clid,a.clname) where a1=@clid<br><br> Fetch Next From Cr_tmp into @clid<br>end<br> Insert into #tmp<br> select '总计','总计','',0,0,0,0,0,0,sum(a10),sum(a11),sum(a12) from #tmp<br>select a1 as 商品编码,a2 as 商品名称,a3 as 种类,a4 as 进货数,a5 as 进货额,a6 as 本期份数,a7 as 最高库存,a8 as 最低库存,a9 as 出货数,a10 as 出货成本,a11 as 出货额,a12 毛利润 from #tmp order by a13<br><br>Close Cr_tmp<br>Deallocate Cr_tmp<br>GO