海信商海導(dǎo)航數(shù)據(jù)庫(kù)常用操作

1、在查詢分析器中輸入1把質(zhì)疑的數(shù)據(jù)庫(kù)更改為緊急模式sp_configure 'allow',1go-reconfigure with overridego-update master.sysdatabasesset status = 32768where name = 'zb'2不能修改進(jìn)價(jià)解決方案if Not Exists(select * from XtFunction where FunCode in ('OP09') begin insert into XtRightFunc(UGrpCode,FunCode,IsInsert,IsUpda

2、te,IsDelete,IsQuery,IsPrint,IsConvert) select UGrpCode,'OP09','0','0','0','0','0','0' from xtUserGrp x where UGrpCode not in ('01','02') and not exists(select 1 from XtRightFunc where UGrpCode=x.UGrpCode and FunCode='OP09

3、9; ) if not exists(select 1 from XtFunction where FunCode='OP09') insert into XtFunction(FunCode,FunName,FunType,DllName,DllFun,DllType,FormType,VER,SerialNo,IsMenu,Remark,IsInsert,IsUpdate,IsDelete,IsQuery,IsPrint,IsConvert,OrderNo,IsChart,ChartSerialNo,ChartFunType,ChartOrderNo,ChartViewPo

4、sition,ChartParentFunCode) values('OP09','采購(gòu)和驗(yàn)收允許修改進(jìn)價(jià)','1','','','0','1','0','0070','0','','0','0','0','0','0','0',10,'2','0900','1',1,'

5、','OP')endGo3部類商品與普通商品的轉(zhuǎn)換命令update goods set plutype='2' where plucode='商品編碼'備注：輸入2是把普通商品轉(zhuǎn)換為部類商品 輸入0是把部類商品轉(zhuǎn)換為普通商品4海信ftp中心ftp:/navigator:hisensoft81 ftp:/navigator:hisensoft0 ftp:/navigator:hisensoft465海信普通商品改部類商品update goods set plutype=&

6、#39;2' where plucode='商品編碼 '另外要把修改商品的供應(yīng)商經(jīng)聯(lián)銷性質(zhì)更改2是部門商品0是普通商品6海信數(shù)據(jù)庫(kù)重見表索引DECLARE DBCCString NVARCHAR(1000)DECLARE TableName VARCHAR(100)DECLARE Cur_Index CURSORFOR SELECT Name AS TblName FROM sysobjects WHERE xType='U' ORDER BY TblNameFOR READ ONLYOPEN Cur_IndexFETCH NEXT FROM Cur_I

7、ndexINTO TableNameWHILE FETCH_STATUS=0BEGIN SET DBCCString = 'DBCC DBREINDEX(TblName,'''')WITH NO_INFOMSGS' EXEC SP_EXECUTESQL DBCCString, N'TblName VARCHAR(100)', TableName PRINT '重建表' + TableName +'的索引.OK!' FETCH NEXT FROM Cur_Index INTO TableNameEND

8、CLOSE Cur_IndexDEALLOCATE Cur_IndexPRINT '操作完成！'7解決部門商品更改為普通商品時(shí)庫(kù)存管理沒(méi)有啟用的問(wèn)題update goods set mngstock='1'1 where plucode='商品編碼8品類進(jìn)銷存GeneralRptName=品類進(jìn)銷存RptComment=按照日期查詢某時(shí)間內(nèi)品類進(jìn)銷存匯總ParametersCount=4ParametersParameter1=BgnDate;起始日期：;3;0;Parameter2=EndDate;截止日期：;3;0;Parameter3=FClsCo

9、de;品類：;5;0;Parameter4=orgcode;門店：;6;0;Columns上期庫(kù)存|數(shù)量=上期庫(kù)存|數(shù)量;0.000;R;2上期庫(kù)存|含稅進(jìn)價(jià)金額=上期庫(kù)存|含稅進(jìn)價(jià)金額;0.00;R;2上期庫(kù)存|無(wú)稅進(jìn)價(jià)金額=上期庫(kù)存|無(wú)稅進(jìn)價(jià)金額;0.00;R;2上期庫(kù)存|售價(jià)金額=上期庫(kù)存|售價(jià)金額;0.00;R;2進(jìn)貨|數(shù)量=進(jìn)貨|數(shù)量;0.000;R;2進(jìn)貨|含稅進(jìn)價(jià)金額=進(jìn)貨|含稅進(jìn)價(jià)金額;0.00;R;2進(jìn)貨|無(wú)稅進(jìn)價(jià)金額=進(jìn)貨|無(wú)稅進(jìn)價(jià)金額;0.00;R;2進(jìn)貨|售價(jià)金額=進(jìn)貨|售價(jià)金額;0.00;R;2退貨|數(shù)量=退貨|數(shù)量;0.000;R;2退貨|含稅進(jìn)價(jià)金額=退貨|含稅進(jìn)

10、價(jià)金額;0.00;R;2退貨|無(wú)稅進(jìn)價(jià)金額=退貨|無(wú)稅進(jìn)價(jià)金額;0.00;R;2退貨|售價(jià)金額=退貨|售價(jià)金額;0.00;R;2調(diào)入|數(shù)量=調(diào)入|數(shù)量;0.000;R;2調(diào)入|含稅進(jìn)價(jià)金額=調(diào)入|含稅進(jìn)價(jià)金額;0.00;R;2調(diào)入|無(wú)稅進(jìn)價(jià)金額=調(diào)入|無(wú)稅進(jìn)價(jià)金額;0.00;R;2調(diào)入|售價(jià)金額=調(diào)入|售價(jià)金額;0.00;R;2調(diào)出|數(shù)量=調(diào)出|數(shù)量;0.000;R;2調(diào)出|含稅進(jìn)價(jià)金額=調(diào)出|含稅進(jìn)價(jià)金額;0.00;R;2調(diào)出|無(wú)稅進(jìn)價(jià)金額=調(diào)出|無(wú)稅進(jìn)價(jià)金額;0.00;R;2調(diào)出|售價(jià)金額=調(diào)出|售價(jià)金額;0.00;R;2配送|數(shù)量=配送|數(shù)量;0.000;R;2配送|含稅進(jìn)價(jià)金額=配送|

11、含稅進(jìn)價(jià)金額;0.00;R;2配送|無(wú)稅進(jìn)價(jià)金額=配送|無(wú)稅進(jìn)價(jià)金額;0.00;R;2配送|售價(jià)金額=配送|售價(jià)金額;0.00;R;2配送退貨|數(shù)量=配送退貨|數(shù)量;0.000;R;2配送退貨|含稅進(jìn)價(jià)金額=配送退貨|含稅進(jìn)價(jià)金額;0.00;R;2配送退貨|無(wú)稅進(jìn)價(jià)金額=配送退貨|無(wú)稅進(jìn)價(jià)金額;0.00;R;2配送退貨|售價(jià)金額=配送退貨|售價(jià)金額;0.00;R;2領(lǐng)用|數(shù)量=領(lǐng)用|數(shù)量;0.000;R;2領(lǐng)用|含稅進(jìn)價(jià)金額=領(lǐng)用|含稅進(jìn)價(jià)金額;0.00;R;2領(lǐng)用|無(wú)稅進(jìn)價(jià)金額=領(lǐng)用|無(wú)稅進(jìn)價(jià)金額;0.00;R;2領(lǐng)用|售價(jià)金額=領(lǐng)用|售價(jià)金額;0.00;R;2盤盈|數(shù)量=盤盈|數(shù)量;0.0

12、00;R;2盤盈|含稅進(jìn)價(jià)金額=盤盈|含稅進(jìn)價(jià)金額;0.00;R;2盤盈|無(wú)稅進(jìn)價(jià)金額=盤盈|無(wú)稅進(jìn)價(jià)金額;0.00;R;2盤盈|售價(jià)金額=盤盈|售價(jià)金額;0.00;R;2盤虧|數(shù)量=盤虧|數(shù)量;0.000;R;2盤虧|含稅進(jìn)價(jià)金額=盤虧|含稅進(jìn)價(jià)金額;0.00;R;2盤虧|無(wú)稅進(jìn)價(jià)金額=盤虧|無(wú)稅進(jìn)價(jià)金額;0.00;R;2盤虧|售價(jià)金額=盤虧|售價(jià)金額;0.00;R;2殘損削價(jià)|數(shù)量=殘損削價(jià)|數(shù)量;0.000;R;2殘損削價(jià)|含稅進(jìn)價(jià)金額=殘損削價(jià)|含稅進(jìn)價(jià)金額;0.00;R;2殘損削價(jià)|無(wú)稅進(jìn)價(jià)金額=殘損削價(jià)|無(wú)稅進(jìn)價(jià)金額;0.00;R;2殘損削價(jià)|售價(jià)金額=殘損削價(jià)|售價(jià)金額;0.00

13、;R;2變價(jià)增值|含稅進(jìn)價(jià)金額=變價(jià)增值|含稅進(jìn)價(jià)金額;0.00;R;2變價(jià)增值|無(wú)稅進(jìn)價(jià)金額=變價(jià)增值|無(wú)稅進(jìn)價(jià)金額;0.00;R;2變價(jià)減值|含稅進(jìn)價(jià)金額=變價(jià)減值|含稅進(jìn)價(jià)金額;0.00;R;2變價(jià)減值|無(wú)稅進(jìn)價(jià)金額=變價(jià)減值|無(wú)稅進(jìn)價(jià)金額;0.00;R;2銷售|數(shù)量=銷售|數(shù)量;0.000;R;2銷售|含稅銷售成本=銷售|含稅銷售成本;0.00;R;2銷售|無(wú)稅銷售成本=銷售|無(wú)稅銷售成本;0.00;R;2銷售|售價(jià)金額=銷售|售價(jià)金額;0.00;R;2批發(fā)|數(shù)量=批發(fā)|數(shù)量;0.000;R;2批發(fā)|含稅成本=批發(fā)|含稅成本;0.00;R;2批發(fā)|無(wú)稅成本=批發(fā)|無(wú)稅成本;0.00;R

14、;2批發(fā)|售價(jià)金額=批發(fā)|售價(jià)金額;0.00;R;2本期庫(kù)存|數(shù)量=本期庫(kù)存|數(shù)量;0.000;R;2本期庫(kù)存|含稅進(jìn)價(jià)金額=本期庫(kù)存|含稅進(jìn)價(jià)金額;0.00;R;2本期庫(kù)存|無(wú)稅進(jìn)價(jià)金額=本期庫(kù)存|無(wú)稅進(jìn)價(jià)金額;0.00;R;2本期庫(kù)存|售價(jià)金額=本期庫(kù)存|售價(jià)金額;0.00;R;2Content/*按照日期查詢某時(shí)間內(nèi)品類進(jìn)銷存報(bào)表使用說(shuō)明：本結(jié)果取自gbk商品臺(tái)賬及goods商品信息表中的品類編碼Created by 秦樹琦 2008-03-12*/ Declare BgnDate char(10) Declare EndDate char(10) Declare OrgCode cha

15、r(10) Declare ClsCode char(10) Declare ClsLevel char(1) -品類總級(jí)別 Declare SelectClsLevel char(1) -當(dāng)前選定的品類級(jí)別 Declare sTmpDate Varchar(10) -臨時(shí)日期 Declare sSql Varchar(8000) select BgnDate=:BgnDate select EndDate=:EndDate select OrgCode=:orgcode select ClsCode=:FClsCode/*查詢品類級(jí)別和當(dāng)前選擇的品類級(jí)別,以便計(jì)算出匯總其下一級(jí)品類的進(jìn)銷存*

16、/ select ClsLevel=ItemValue From SysCfg where Section = 'SYSTEM' and ItemName = 'CLSLEVEL' if ClsCode = '000000' set SelectClsLevel='0' else select SelectClsLevel=ClsLevel from GClass where ClsCode = ClsCode /*刪除舊的視圖V_GBK*/ if exists (select * from dbo.sysobjects wher

17、e id = object_id(N'dbo.V_GBK') and OBJECTPROPERTY(id, N'IsView') = 1) drop view dbo.V_GBK SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON /*設(shè)置基本語(yǔ)句指定時(shí)間gbk匯總到視圖V_GBK*/ set sTmpDate=BgnDate while not Exists(Select Name from sysobjects where name='GBK'+SubString(sTmpDate,1,4)+SubStrin

18、g(sTmpDate,6,2) and XType='U') begin Set sTmpDate=Convert(char(10),DateAdd(Month,1,sTmpDate),120) end Set sSQL='CREATE VIEW dbo.V_GBK AS SELECT * FROM GBK'+SubString(sTmpDate,1,4)+SubString(sTmpDate,6,2)Set sTmpDate=Convert(char(10),DateAdd(Month,1,sTmpDate),120) While (SubString(sTm

19、pDate,1,4)+SubString(sTmpDate,6,2)<=(SubString(EndDate,1,4)+SubString(EndDate,6,2)/*設(shè)置Union語(yǔ)句*/BeginIf Exists(Select Name from sysobjects where name='GBK'+SubString(sTmpDate,1,4)+SubString(sTmpDate,6,2) and XType='U') Begin-Union Set sSQL=sSQL+' union allSELECT * FROM GBK'

20、+SubString(sTmpDate,1,4)+SubString(sTmpDate,6,2) End Set sTmpDate=Convert(char(10),DateAdd(Month,1,sTmpDate),120)End/*執(zhí)行命令，更新視圖語(yǔ)句*/Exec(sSql)-SET QUOTED_IDENTIFIER OFF -SET ANSI_NULLS OFF - select * from V_GBK=/*創(chuàng)建臨時(shí)表#TmpGBk*/if exists (select * from tempdb.sysobjects where id = object_id(N'#Tmp

21、GBk') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table #TmpGBkCreate table #TmpGBk(AccDate char (10) NOT NULL, OrgCode char (6) NOT NULL Default (''), PluCode char (20) NOT NULL, DepCode char (6) NOT NULL, SqCost money NOT NULL DEFAULT (0), SqNetCost money NOT NULL DEFAULT

22、(0), SqTotal money NOT NULL DEFAULT (0), SqCount money NOT NULL DEFAULT (0), JhCost money NOT NULL DEFAULT (0), JhNetCost money NOT NULL DEFAULT (0), JhTotal money NOT NULL DEFAULT (0), JhCount money NOT NULL DEFAULT (0), ThCost money NOT NULL DEFAULT (0), ThNetCost money NOT NULL DEFAULT (0), ThTot

23、al money NOT NULL DEFAULT (0), ThCount money NOT NULL DEFAULT (0), DrCost money NOT NULL DEFAULT (0), DrNetCost money NOT NULL DEFAULT (0), DrTotal money NOT NULL DEFAULT (0), DrCount money NOT NULL DEFAULT (0), DcCost money NOT NULL DEFAULT (0), DcNetCost money NOT NULL DEFAULT (0), DcTotal money N

24、ULL DEFAULT (0), DcCount money NULL DEFAULT (0), ZpCost money NOT NULL DEFAULT (0), ZpNetCost money NOT NULL DEFAULT (0), ZpTotal money NOT NULL DEFAULT (0), ZpAmount money NOT NULL DEFAULT (0), ZpCount money NOT NULL DEFAULT (0), ZtCost money NOT NULL DEFAULT (0), ZtNetCost money NOT NULL DEFAULT (

25、0), ZtTotal money NOT NULL DEFAULT (0), ZtAmount money NOT NULL DEFAULT (0), ZtCount money NOT NULL DEFAULT (0), LyCost money NOT NULL DEFAULT (0), LyNetCost money NOT NULL DEFAULT (0), LyTotal money NOT NULL DEFAULT (0), LyCount money NOT NULL DEFAULT (0), PyCost money NOT NULL DEFAULT (0), PyNetCo

26、st money NOT NULL DEFAULT (0), PyTotal money NOT NULL DEFAULT (0), PyCount money NOT NULL DEFAULT (0), PkCost money NOT NULL DEFAULT (0), PkNetCost money NOT NULL DEFAULT (0), PkTotal money NOT NULL DEFAULT (0), PkCount money NOT NULL DEFAULT (0), XjCost money NOT NULL DEFAULT (0), XjNetCost money N

27、OT NULL DEFAULT (0), XjTotal money NOT NULL DEFAULT (0), XjCount money NOT NULL DEFAULT (0), TkCost money NOT NULL DEFAULT (0), TkNetCost money NOT NULL DEFAULT (0), TjCost money NOT NULL DEFAULT (0), TjNetCost money NOT NULL DEFAULT (0), TkTotal money NOT NULL DEFAULT (0), TjTotal money NOT NULL DE

28、FAULT (0), XsCost money NOT NULL DEFAULT (0), XsNetCost money NOT NULL DEFAULT (0), XsTotal money NOT NULL DEFAULT (0), XsAmount money NOT NULL DEFAULT (0), XsCount money NOT NULL DEFAULT (0), DscTotal money NOT NULL DEFAULT (0), PfSaleCount money NOT NULL DEFAULT (0), PfSaleCost money NOT NULL DEFA

29、ULT (0), PfSaleNetCost money NOT NULL DEFAULT (0), PfSaleTotal money NOT NULL DEFAULT (0), PfSaleAmount money NOT NULL DEFAULT (0), PfReturnCount money NOT NULL DEFAULT (0), PfReturnCost money NOT NULL DEFAULT (0), PfReturnNetCost money NOT NULL DEFAULT (0), PfReturnTotal money NOT NULL DEFAULT (0),

30、 PfReturnAmount money NOT NULL DEFAULT (0), BqCost money NOT NULL DEFAULT (0), BqNetCost money NOT NULL DEFAULT (0), BqTotal money NOT NULL DEFAULT (0), BqCount money NOT NULL DEFAULT (0), CONSTRAINT PK_#TmpGBk PRIMARY KEY (AccDate,OrgCode,PluCode,DepCode) )-插入編碼、部門、組織 Insert into #TmpGBk (AccDate,P

31、luCode,DepCode,OrgCode) select Distinct'',PluCode,DepCode,OrgCode from V_GBK A Where AccDate <= EndDate or FormType='Z' -更新上期結(jié)存數(shù)=上期結(jié)存數(shù)Update A Set A.SqCount=G.JcCount,A.SqCost=G.JcCost,A.SqNetCost=G.JcNetCost,A.SqTotal=G.JcTotal from #TmpGBk A,V_GBK G where A.PluCode=G.PluCode and

32、 A.DepCode=G.DepCode and A.OrgCode=G.OrgCode and G.FormType='Z' and month(g.accdate)=month(bgndate) and year(g.accDate)=year(BgnDate) -用發(fā)生數(shù)更新上期結(jié)存數(shù)Update G Set G.SqCount=G.SqCount+B.FsCount,G.SqCost=G.SqCost+B.FsCost,G.SqNetCost=G.SqNetCost+B.FsNetCost,G.SqTotal=G.SqTotal+B.FsTotal from #TmpG

33、Bk G, (select PluCode,DepCode,OrgCode, Sum(Case When FormType in('3','5','6','7','A','E','F','G') then -fsCount When FormType in ('4','8') then 0 Else FsCount End) as FsCount, Sum(Case When FormType in('3','5

34、','6','7','A','E','F','G') then -fsCost Else FsCost End) as FsCost, Sum(Case When FormType in('3','5','6','7','A','E','F','G') then -fsNetCost Else fsNetCost End) as fsNetCost, Sum(Cas

35、e When FormType in('3','5','6','7','A','E','F','G') then -fsTotal Else fsTotal End) as fsTotal from V_GBK A where AccDate< BgnDate group by a.plucode,a.depcode,code ) B WHERE G.PluCode=B.PluCode and G.DepCode=B.DepCode and G.Org

36、Code=B.OrgCode -goInsert into #TmpGBk(AccDate,PluCode,DepCode,OrgCode) select Distinct '',PluCode,DepCode,OrgCode from V_GBK A Where AccDate Between BgnDate and EndDate and PluCode+'-'+DepCode+'-'+OrgCode Not in (select PluCode+'-'+DepCode+'-'+OrgCode from #Tm

37、pGBk) -更新發(fā)生數(shù)Update G Set G.JhCount=G.JhCount+B.JhCount,G.JhCost=G.JhCost+B.JhCost,G.JhNetCost=G.JhNetCost+B.JhNetCost,G.JhTotal=G.JhTotal+B.JhTotal, G.ThCount=G.ThCount+B.ThCount,G.ThCost=G.ThCost+B.ThCost,G.ThNetCost=G.ThNetCost+B.ThNetCost,G.ThTotal=G.ThTotal+B.ThTotal, G.DrCount=G.DrCount+B.DrCou

38、nt,G.DrCost=G.DrCost+B.DrCost,G.DrNetCost=G.DrNetCost+B.DrNetCost,G.DrTotal=G.DrTotal+B.DrTotal, G.DcCount=G.DcCount+B.DcCount,G.DcCost=G.DcCost+B.DcCost,G.DcNetCost=G.DcNetCost+B.DcNetCost,G.DcTotal=G.DcTotal+B.DcTotal, G.ZpCount=G.ZpCount+B.ZpCount,G.ZpCost=G.ZpCost+B.ZpCost,G.ZpNetCost=G.ZpNetCos

39、t+B.ZpNetCost,G.ZpTotal=G.ZpTotal+B.ZpTotal, G.ZtCount=G.ZtCount+B.ZtCount,G.ZtCost=G.ZtCost+B.ZtCost,G.ZtNetCost=G.ZtNetCost+B.ZtNetCost,G.ZtTotal=G.ZtTotal+B.ZtTotal, G.LyCount=G.LyCount+B.LyCount,G.LyCost=G.LyCost+B.LyCost,G.LyNetCost=G.LyNetCost+B.LyNetCost,G.LyTotal=G.LyTotal+B.LyTotal, G.PyCou

40、nt=G.PyCount+B.PyCount,G.PyCost=G.PyCost+B.PyCost,G.PyNetCost=G.PyNetCost+B.PyNetCost,G.PyTotal=G.PyTotal+B.PyTotal, G.PkCount=G.PkCount+B.PkCount,G.PkCost=G.PkCost+B.PkCost,G.PkNetCost=G.PkNetCost+B.PkNetCost,G.PkTotal=G.PkTotal+B.PkTotal, G.XjCount=G.XjCount+B.XjCount,G.XjCost=G.XjCost+B.XjCost,G.

41、XjNetCost=G.XjNetCost+B.XjNetCost,G.XjTotal=G.XjTotal+B.XjTotal, G.TkCost=G.TkCost+B.TkCost,G.TkNetCost=G.TkNetCost+B.TkNetCost,G.TkTotal=G.TkTotal+B.TkTotal, G.TjCost=G.TjCost+B.TjCost,G.TjNetCost=G.TjNetCost+B.TjNetCost,G.TjTotal=G.TjTotal+B.TjTotal, G.XsCount=G.XsCount+B.XsCount,G.XsCost=G.XsCost

42、+B.XsCost,G.XsNetCost=G.XsNetCost+B.XsNetCost,G.XsTotal=G.XsTotal+B.XsTotal, G.PfSaleCount=G.PfSaleCount+B.PfSaleCount,G.PfSaleCost=G.PfSaleCost+B.PfSaleCost,G.PfSaleNetCost=G.PfSaleNetCost+B.PfSaleNetCost,G.PfSaleTotal=G.PfSaleTotal+B.PfSaleTotal, G.PfReturnCount=G.PfReturnCount+B.PfReturnCount,G.P

43、fReturnCost=G.PfReturnCost+B.PfReturnCost,G.PfReturnNetCost=G.PfReturnNetCost+B.PfReturnNetCost,G.PfReturnTotal=G.PfReturnTotal+B.PfReturnTotal from #TmpGBk G, (select PluCode,DepCode,OrgCode, Sum(Case FormType when '0' then FsCount else 0 end ) JhCount, Sum(Case when (FormType='1')

44、and (FsCount>0) or (FormType='D') then FsCount when (FormType='F') or (FormType='G') and (FsCount<0) then FsCount*(-1) else 0 end ) DrCount, Sum(Case when (FormType='1') and (FsCount<0) or (FormType='C') then FsCount*(-1) when (FormType='F') o

45、r (FormType='G') and (FsCount>0) then FsCount else 0 end ) DcCount, Sum(Case when (FormType='2') or (FormType='9') and (FsCount>0) then FsCount else 0 end ) PyCount,Sum(Case when (FormType='2') or (FormType='9') and (FsCount<0) then FsCount*(-1) else

46、0 end ) PkCount, Sum(Case FormType when '3' then FsCount else 0 end ) XjCount, Sum(Case FormType when '5' then FsCount else 0 end ) ThCount, Sum(Case FormType when '6' then FsCount else 0 end ) XsCount, Sum(Case FormType when '7' then FsCount else 0 end ) LyCount, Sum

47、(Case FormType when 'A' then FsCount else 0 end ) PfSaleCount, Sum(Case FormType when 'B' then FsCount else 0 end ) PfReturnCount, Sum(Case when (FormType='E') and (FsCount>0) then FsCount else 0 end ) ZpCount, Sum(Case when (FormType='E') and (FsCount<0) then F

48、sCount*(-1) else 0 end ) ZtCount, Sum(Case FormType when '0' then FsCost else 0 end ) JhCost, Sum(Case when (FormType='1') and (FsCount>0) or (FormType='D') then FsCost when (FormType='F') or (FormType='G') and (FsCount<0) then FsCost*(-1) else 0 end ) D

49、rCost, Sum(Case when (FormType='1') and (FsCount<0) or (FormType='C') then FsCost*(-1) when (FormType='F') or (FormType='G') and (FsCount>0) then FsCost else 0 end ) DcCost, Sum(Case when (FormType='2') or (FormType='9') and (FsCost>0) then Fs

50、Cost else 0 end ) PyCost,Sum(Case when (FormType='2') or (FormType='9') and (FsCost<0) then FsCost*(-1) else 0 end ) PkCost, Sum(Case FormType when '3' then FsCost else 0 end ) XjCost, Sum(Case when (FormType='4') and (FsCost)<0 then FsCost*(-1) else 0 end ) TkC

51、ost, Sum(Case when (FormType='4') and (FsCost)>0 then FsCost else 0 end ) TjCost, Sum(Case FormType when '5' then FsCost else 0 end ) ThCost, Sum(Case FormType when '6' then FsCost else 0 end ) XsCost, Sum(Case FormType when '7' then FsCost else 0 end ) LyCost, Sum

52、(Case FormType when 'A' then FsCost else 0 end ) PfSaleCost, Sum(Case FormType when 'B' then FsCost else 0 end ) PfReturnCost, Sum(Case when (FormType='E') and (FsCost>0) then FsCost else 0 end ) ZpCost, Sum(Case when (FormType='E') and (FsCost<0) then FsCost*(-

53、1) else 0 end ) ZtCost, Sum(Case FormType when '0' then FsNetCost else 0 end ) JhNetCost, Sum(Case when (FormType='1') and (FsCount>0) or (FormType='D') then FsNetCost when (FormType='F') or (FormType='G') and (FsCount<0) then FsNetCost*(-1) else 0 end )

54、 DrNetCost, Sum(Case when (FormType='1') and (FsCount<0) or (FormType='C') then FsNetCost*(-1) when (FormType='F') or (FormType='G') and (FsCount>0) then FsNetCost else 0 end ) DcNetCost, Sum(Case when (FormType='2') or (FormType='9') and (FsNetC

55、ost>0) then FsNetCost else 0 end ) PyNetCost,Sum(Case when (FormType='2') or (FormType='9') and (FsNetCost<0) then FsNetCost*(-1) else 0 end ) PkNetCost, Sum(Case FormType when '3' then FsNetCost else 0 end ) XjNetCost, Sum(Case when (FormType='4') and (FsNetCos

56、t)<0 then FsNetCost*(-1) else 0 end ) TkNetCost, Sum(Case when (FormType='4') and (FsNetCost)>0 then FsNetCost else 0 end ) TjNetCost, Sum(Case FormType when '5' then FsNetCost else 0 end ) ThNetCost, Sum(Case FormType when '6' then FsNetCost else 0 end ) XsNetCost, Sum

57、(Case FormType when '7' then FsNetCost else 0 end ) LyNetCost, Sum(Case FormType when 'A' then FsNetCost else 0 end ) PfSaleNetCost, Sum(Case FormType when 'B' then FsNetCost else 0 end ) PfReturnNetCost, Sum(Case when (FormType='E') and (FsNetCost>0) then FsNetCos

58、t else 0 end ) ZpNetCost, Sum(Case when (FormType='E') and (FsNetCost<0) then FsNetCost*(-1) else 0 end ) ZtNetCost, Sum(Case FormType when '0' then FsTotal else 0 end ) JhTotal, Sum(Case when (FormType='1') and (FsCount>0) or (FormType='D') then FsTotal when (F

59、ormType='F') or (FormType='G') and (FsCount<0) then FsTotal*(-1) else 0 end ) DrTotal, Sum(Case when (FormType='1') and (FsCount<0) or (FormType='C') then FsTotal*(-1) when (FormType='F') or (FormType='G') and (FsCount>0) then FsTotal else 0 e

60、nd ) DcTotal, Sum(Case when (FormType='2') or (FormType='9') and (FsTotal>0) then FsTotal else 0 end ) PyTotal,Sum(Case when (FormType='2') or (FormType='9') and (FsTotal<0) then FsTotal*(-1) else 0 end ) PkTotal, Sum(Case FormType when '3' then FsTotal

61、else 0 end ) XjTotal, Sum(Case FormType when '5' then FsTotal else 0 end ) ThTotal, Sum(Case FormType when '6' then FsTotal else 0 end ) XsTotal, Sum(Case FormType when '7' then FsTotal else 0 end ) LyTotal, Sum(Case when (FormType='8') and (FsTotal)<0 then FsTotal

62、*(-1) else 0 end ) TkTotal, Sum(Case when (FormType='8') and (FsTotal)>0 then FsTotal else 0 end ) TjTotal, Sum(Case FormType when 'A' then FsTotal else 0 end ) PfSaleTotal, Sum(Case FormType when 'B' then FsTotal else 0 end ) PfReturnTotal, Sum(Case when (FormType='E') and (FsTotal>0) then FsTotal else 0 end ) ZpTotal, Sum(Case wh