CREATE PROCEDURE sp_pro_invQuery (@sdate char(9)='20030201',@edate char(9)='20030228',@Asdate char(9)='20030201',@Aedate char(9)='20030228') AS SET NOCOUNT ON select case when (grouping(item.code)=1) then 'Total' else isnull(item.code,'unknown') end as code ,sum(isnull(#esie.esie_inventory_end,0) +isnull(#fjes.fjesend ,0) ) as FJES_ESIE_inv /* isnull(#agent.agent_inventory_end,0)+*/ ,sum(isnull(#fjes.fjesend ,0)) as FJES_inv ,sum(#esie.esie_inventory_end) as ESIE_inv ,sum(#esie.esiesaleNum) as ESIEsale ,sum(#agent.agent_inventory_end) as DIS_inv, sum(#agent.agentsale) AS DISsale into #temp2 from (((item left join (select item.code,item.simple_name,item.agent_inv_fst,isnull(I.AgentImport,0) as agentimport,isnull(O.AgentSale,0) as agentsale,item.agent_inv_fst+isnull(I.AgentImport,0)-isnull(O.AgentSale,0) as Agent_Inventory_End from ((item left join (select code,sum(AGENT_IMPNum) as AgentImport from AGENTIMP where AGENT_IMPdate>= @Asdate and AGENT_IMPdate<=@Aedate group by code ) as I on item.code=I.code) left join (select code,sum(agent_saleNum) as Agentsale from agentsale where agent_saledate>= @Asdate and agent_saledate<= @Aedate group by code) as O on item.code=O.code )) as #agent on substring(item.code,1,10)=substring(#agent.code,1,10)) left join (select item.code,item.simple_name,item.esie_inv_fst,isnull(I.esieImportNum,0) as esieimport,isnull(O.EsieSalenum,0) as esiesaleNum,item.Esie_inv_fst+isnull(I.EsieImportNum,0)-isnull(O.EsieSaleNum,0) as Esie_Inventory_End from ((item left join (select code,sum(Esie_sale_Num) as EsiesaleNum from esiesale where Esie_saledate>= @sdate and Esie_saledate<=@edate group by code ) as O on item.code=O.code) left join (select substring(code,1,10) as code,sum(esie_import_Num) as esieImportNum from esieimport where esie_import_date>= @sdate and esie_import_date<= @edate and esieImport.customer = 'Z03001' group by substring(code,1,10)) as I on item.code=I.code ) ) as #esie on substring(item.code,1,10)=substring(#esie.code,1,10)) left join ( select code,FJES_inv as fjesEnd from v_tpics_inv_T) as #fjes on substring(item.code,1,10)=substring(#fjes.code,1,10)) group by item.code with rollup select item.simple_name as model,#temp2.* from #temp2 left join item on #temp2.code=item.code order by code SET NOCOUNT OFF
pengdali(大力 V2.0) ( ) ,好快support
我已解决这个问题,谢谢各位的发言。 思路共享: select case when (grouping(item.code)=1) then 'Total' else isnull(item.code,'unknown') end as code, case when (grouping(item.code)=1) then ' ' else min(item.simple_name) end as Model ,sum(isnull(#esie.esie_inventory_end,0) +isnull(#fjes.fjesend ,0) ) as FJES_ESIE_inv /* isnull(#agent.agent_inventory_end,0)+*/ ,sum(isnull(#fjes.fjesend ,0)) as FJES_inv ,sum(#esie.esie_inventory_end) as ESIE_inv ,sum(#esie.esiesaleNum) as ESIEsale ,sum(#agent.agent_inventory_end) as DIS_inv, sum(#agent.agentsale) AS DISsale from (((item left join (select item.code,item.simple_name,item.agent_inv_fst,isnull(I.AgentImport,0) as agentimport,isnull(O.AgentSale,0) as agentsale,item.agent_inv_fst+isnull(I.AgentImport,0)-isnull(O.AgentSale,0) as Agent_Inventory_End from ((item left join (select code,sum(AGENT_IMPNum) as AgentImport from AGENTIMP where AGENT_IMPdate>= @Asdate and AGENT_IMPdate<=@Aedate group by code ) as I on item.code=I.code) left join (select code,sum(agent_saleNum) as Agentsale from agentsale where agent_saledate>= @Asdate and agent_saledate<= @Aedate group by code) as O on item.code=O.code )) as #agent on substring(item.code,1,10)=substring(#agent.code,1,10)) left join (select item.code,item.simple_name,item.esie_inv_fst,isnull(I.esieImportNum,0) as esieimport,isnull(O.EsieSalenum,0) as esiesaleNum,item.Esie_inv_fst+isnull(I.EsieImportNum,0)-isnull(O.EsieSaleNum,0) as Esie_Inventory_End from ((item left join (select code,sum(Esie_sale_Num) as EsiesaleNum from esiesale where Esie_saledate>= @sdate and Esie_saledate<=@edate group by code ) as O on item.code=O.code) left join (select substring(code,1,10) as code,sum(esie_import_Num) as esieImportNum from esieimport where esie_import_date>= @sdate and esie_import_date<= @edate and esieImport.customer = 'Z03001' group by substring(code,1,10)) as I on item.code=I.code ) ) as #esie on substring(item.code,1,10)=substring(#esie.code,1,10)) left join ( select code,FJES_inv as fjesEnd from v_tpics_inv_T) as #fjes on substring(item.code,1,10)=substring(#fjes.code,1,10)) group by item.code with rollup
AS
SET NOCOUNT ON
select case when (grouping(item.code)=1) then 'Total'
else isnull(item.code,'unknown')
end as code
,sum(isnull(#esie.esie_inventory_end,0) +isnull(#fjes.fjesend ,0) ) as FJES_ESIE_inv /* isnull(#agent.agent_inventory_end,0)+*/
,sum(isnull(#fjes.fjesend ,0)) as FJES_inv
,sum(#esie.esie_inventory_end) as ESIE_inv ,sum(#esie.esiesaleNum) as ESIEsale
,sum(#agent.agent_inventory_end) as DIS_inv, sum(#agent.agentsale) AS DISsale into #temp2 from
(((item left join
(select item.code,item.simple_name,item.agent_inv_fst,isnull(I.AgentImport,0) as agentimport,isnull(O.AgentSale,0) as agentsale,item.agent_inv_fst+isnull(I.AgentImport,0)-isnull(O.AgentSale,0)
as Agent_Inventory_End from ((item left join (select code,sum(AGENT_IMPNum) as AgentImport from AGENTIMP where AGENT_IMPdate>= @Asdate and AGENT_IMPdate<=@Aedate
group by code ) as I on item.code=I.code)
left join (select code,sum(agent_saleNum) as Agentsale from agentsale where agent_saledate>= @Asdate and agent_saledate<= @Aedate
group by code) as O on item.code=O.code )) as #agent on substring(item.code,1,10)=substring(#agent.code,1,10)) left join
(select item.code,item.simple_name,item.esie_inv_fst,isnull(I.esieImportNum,0) as esieimport,isnull(O.EsieSalenum,0) as esiesaleNum,item.Esie_inv_fst+isnull(I.EsieImportNum,0)-isnull(O.EsieSaleNum,0)
as Esie_Inventory_End from ((item left join (select code,sum(Esie_sale_Num) as EsiesaleNum from esiesale where Esie_saledate>= @sdate and Esie_saledate<=@edate
group by code ) as O on item.code=O.code) left join (select substring(code,1,10) as code,sum(esie_import_Num) as esieImportNum from esieimport where esie_import_date>= @sdate and esie_import_date<= @edate
and esieImport.customer = 'Z03001' group by substring(code,1,10)) as I on item.code=I.code ) ) as #esie on substring(item.code,1,10)=substring(#esie.code,1,10)) left join
( select code,FJES_inv as fjesEnd from v_tpics_inv_T)
as #fjes on substring(item.code,1,10)=substring(#fjes.code,1,10)) group by item.code with rollup select item.simple_name as model,#temp2.* from #temp2 left join item on #temp2.code=item.code order by code
SET NOCOUNT OFF
思路共享:
select
case when (grouping(item.code)=1) then 'Total'
else isnull(item.code,'unknown')
end as code,
case when (grouping(item.code)=1) then ' '
else min(item.simple_name)
end as Model
,sum(isnull(#esie.esie_inventory_end,0) +isnull(#fjes.fjesend ,0) ) as FJES_ESIE_inv /* isnull(#agent.agent_inventory_end,0)+*/
,sum(isnull(#fjes.fjesend ,0)) as FJES_inv
,sum(#esie.esie_inventory_end) as ESIE_inv ,sum(#esie.esiesaleNum) as ESIEsale
,sum(#agent.agent_inventory_end) as DIS_inv, sum(#agent.agentsale) AS DISsale from
(((item left join
(select item.code,item.simple_name,item.agent_inv_fst,isnull(I.AgentImport,0) as agentimport,isnull(O.AgentSale,0) as agentsale,item.agent_inv_fst+isnull(I.AgentImport,0)-isnull(O.AgentSale,0)
as Agent_Inventory_End from ((item left join (select code,sum(AGENT_IMPNum) as AgentImport from AGENTIMP where AGENT_IMPdate>= @Asdate and AGENT_IMPdate<=@Aedate
group by code ) as I on item.code=I.code)
left join (select code,sum(agent_saleNum) as Agentsale from agentsale where agent_saledate>= @Asdate and agent_saledate<= @Aedate
group by code) as O on item.code=O.code )) as #agent on substring(item.code,1,10)=substring(#agent.code,1,10)) left join
(select item.code,item.simple_name,item.esie_inv_fst,isnull(I.esieImportNum,0) as esieimport,isnull(O.EsieSalenum,0) as esiesaleNum,item.Esie_inv_fst+isnull(I.EsieImportNum,0)-isnull(O.EsieSaleNum,0)
as Esie_Inventory_End from ((item left join (select code,sum(Esie_sale_Num) as EsiesaleNum from esiesale where Esie_saledate>= @sdate and Esie_saledate<=@edate
group by code ) as O on item.code=O.code) left join (select substring(code,1,10) as code,sum(esie_import_Num) as esieImportNum from esieimport where esie_import_date>= @sdate and esie_import_date<= @edate
and esieImport.customer = 'Z03001' group by substring(code,1,10)) as I on item.code=I.code ) ) as #esie on substring(item.code,1,10)=substring(#esie.code,1,10)) left join
( select code,FJES_inv as fjesEnd from v_tpics_inv_T)
as #fjes on substring(item.code,1,10)=substring(#fjes.code,1,10)) group by item.code with rollup