如果出现过，请教一下处理方法。（此问题比较特殊，所以发在博客中）

--如果順序真的那麼重要的話，借用下臨時表吧。

--創建測試環境
Create Table T(FYear Int, FPeriod Int, 材料名称 Nvarchar(20), 数量 Int, 单价 Numeric(20,6), 成本 Numeric(20,6))
--插入數據
Insert Into T(FYear, FPeriod, 材料名称, 数量, 单价, 成本)
Select 2007,1,N'葡萄糖', 101,3.6,363.6
Union All Select 2007,2,N'葡萄糖', 102,4.2,428.4
Union All Select 2007,9,N'葡萄糖', 102,4.2,428.4
Union All Select 2007,10,N'葡萄糖', 102,4.2,428.4
Union All Select 2007,12,N'葡萄糖', 102,4.2,428.4
Union All Select 2007,1,N'收缩膜', 101,4.1,414.1
Union All Select 2007,2,N'收缩膜',  102,4.2,428.4
Union All Select 2007,1,N'丁基胶塞1',101,4.4,444.4
Union All Select 2007,2,N'丁基胶塞1',101,4.4,444.4
Union All Select 2007,3,N'丁基胶塞1',101,4.4,444.4
GO
--測試
Select ID = Identity(Int, 1, 1), * Into #T From T

Declare @Max Int, @I Int, @S Nvarchar(4000)
Select @Max = 12, @I = 1, @S = N'Select 材料名称'
While @I <= @Max
Begin
Select @S=@S + ', SUM(Case FPeriod When ' + Cast(@I As Varchar) + N' Then 数量 Else 0 End) As [' + Right(100 + @I, 2) + N'数量]'
+ ', SUM(Case FPeriod When ' + Cast(@I As Varchar) + N' Then 单价 Else 0 End) As [' + Right(100 + @I, 2) + N'单价]'
+ ', SUM(Case FPeriod When ' + Cast(@I As Varchar) + N' Then 成本 Else 0 End) As [' + Right(100 + @I, 2) + N'成本]'
Select @I=@I+1
End
Select @S = @S+ N' , SUM(数量) As 全年数量, SUM(单价) As 全年单价, SUM(成本) As 全年成本 From #T Group By FYear, 材料名称 Order By Min(ID) '
EXEC(@S)
Drop Table #T
GO
--刪除測試環境
Drop Table T

 

 

--此存储过程的关键是保证了数据按原来的顺序，成本表企业往往要求按特定的排序规则。如医药企业的成本表，首先是医药原料及辅料再包装再费用，每部分要小计，再总计。k/3系统是不可能直接获得这些表的，需要相应的二次开发。

 

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER                    procedure WY_Sp_XSFPPM
(
-- 查询类型
-- SelType=1 精确查询 
-- SelType=0 模糊查询 
@SelType bit=1,

-- ***精确查询参数***
-- 参数    项目       数据类型
--  起 止 
-- 时间 √ √ 时间
-- 物料 √ √ 编码
-- 客户 √ √ 编码
-- 仓库 √ √ 编码
-- 操作员 -- 名称
-- 部门         -- 编码
-- 自定义1：
-- 自定义2：
-- 精确查询时，必须同时输入起止值
-- 所有的输入变量必须有默认值
@J_Operator  varchar(80)=null,

@J_DeptBegin  varchar(80)=null,@J_DeptEnd  varchar(80)=null,

@J_TimerBegin datetime='2006-01-01 00:00:00.000',
@J_TimerEnd   datetime='2006-12-31 00:00:00.000',

@J_ItemBegin varchar(80)='F.000000',
@J_ItemEnd   varchar(80)='G.ZZZZZZ',

@J_OrgBegin  varchar(80)=null,
@J_OrgEnd varchar(80)=null,

@J_StockBegin varchar(80)=null,
@J_StockEnd varchar(80)=null,

@J_udtVar1  varchar(80)=null,
@J_udtVar2  varchar(80)=null,

-- *** 模糊查询参数 ***
-- 参数   数据类型
-- 时间    时间
-- 物料    编码
-- 客户    编码
-- 仓库    编码
-- 操作员  名称
-- 部门    编码
-- 自定义1：
-- 自定义2：  
-- 所有的输入变量必须有默认值 varchar(80) 的默认值是'%'
@M_Operator  varchar(80)='%',

@M_Dept  varchar(80)='%',

@M_Timer datetime='1998-01-02 00:00:00.000',

@M_Item varchar(80)='%',

@M_Org  varchar(80)='%',

@M_Stock  varchar(80)='%',

@M_udtVar1  varchar(80)='%',

@M_udtVar2  varchar(80)='%',

@Type  nchar(1)=N'年',  --排行榜处理类型(日、周、月、季、年)
@Date  varchar(355)= null,   --排行榜日期,不指定为当前日期
@TopN int=1000,          --记录数
@p_dt_begin varchar(355)= null,
@dt_begin varchar(355)= null
   )
AS
begin

set nocount on
declare @pItemID int

IF @Date IS NULL
 SET @Date=CONVERT(char(10),GETDATE(),120)

ELSE
 SET @Date=CONVERT(char(10),@Date,120)
IF ISNULL(@TopN,0)<1
 SET @TopN=100
--print @type
--根据@Type决定计算的起始日期
IF @Type=N'年'
 SELECT
  @dt_begin=CONVERT(char(5),@Date,120)+'01-01',
  @Date=convert(char(5),DATEADD(Year,1,@dt_begin),120)+'01-01',
                @p_dt_begin=convert(char(5),DATEADD(Year,-1,@dt_begin),120)+'01-01'
--if not exists(select * from t_item where FItemClassID=4 and FNumber=@J_ItemBegin)
-- begin
--  RAISERROR('【物料】不存在！',18,18)
--  GOTO H_Error
-- end
--else
-- begin
--  select @pItemID=FItemID from t_item where FItemClassID=4 and FNumber=@J_ItemBegin
-- end

--建立排名表

CREATE TABLE #DATA(
     FDate datetime null,
     FEmpName varchar(355) null,--业务员姓名
     FNumber  varchar(355) null,--物料长代码
     FShortNumber  varchar(355) null,--物料短代码
     FCustID  int null,--客户内码
     FCustNumber  varchar(355) null,--客户长代码
     FShortCustNumber  varchar(355) null,--客户短代码
     FName  varchar(355) null,--物料名称
     FModel  varchar(355) null,--物料规格
     FUnitName  varchar(355) null,--物料单位
     FQtyDecimal smallint null, --数量精度
     FPriceDecimal smallint null, --价格精度
     FQty     decimal(28,10)  null,--数量
     FCUUnitName  varchar(355) null,
     FCUUnitQty     decimal(28,10)  null,
     FPrice   decimal(28,10)  null,--价格
     FAmount  decimal(28,10)  null,--金额
     FTaxRate  decimal(28,10)  null,--税率
     FTaxAmount  decimal(28,10)  null,--税额
     FCountAmount money null

--合计金额
--     FID int IDENTITY
     )

 INSERT INTO #DATA  SELECT
v1.fdate,
CONVERT(varchar(355),t4.FName),
 t1.FNumber,
'',
V1.FCustID,
'',
'',
'',
'',
'',
6,
4,
 v2.FQty,
 '',
ISNull(v2.FQty,0)/(Case When ISNULL(t5.FCoefficient,0)=0 Then 1 Else t5.FCoefficient End ),
0,
ROUND((CASE v1.FTranType WHEN 80 THEN v2.FStdAmount   else v2.FStdAmount- v2.FStdTaxAmount END),2),
0,
ROUND(v2.FStdTaxAmount,2),
Round(case v1.FTranType when 80 then (v2.FStdAmount + v2.FStdTaxAmount)  else v2.FStdAmount end,2)
FROM ICSale v1,
     ICSaleEntry v2,
     t_ICItem t1,
     t_Organization t2,
     t_Department t3,
     t_Emp t4,
     t_MeasureUnit t5 
WHERE v1.FInterID=v2.FInterID
  AND v1.FTranType in (80,86) 
  AND v1.FCancelLation=0
  AND convert(datetime,convert(varchar(30),v1.FDate,111)) >=@p_dt_begin
  AND convert(datetime,convert(varchar(30),v1.FDate,111)) <=@J_TimerEnd  
  AND v2.FItemID=t1.FItemID
  AND v1.FCustID=t2.FItemID
  AND v1.FDeptID =t3.FItemID
  AND v1.FEmpID =t4.FItemID
  AND t1.FCUUnitID = t5.FMeasureUnitID
  AND t1.FNumber>=@J_ItemBegin AND t1.FNumber<=@J_ItemEnd
 --order  BY t4.FName,t1.FNumber

 UPDATE t1 SET t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel, t1.FCUUnitName=(Select FName From T_MeasureUnit Where FMeasureUnitID = t2.FCUUnitID ),  t1.FUnitName=t3.FName,t1.FQtyDecimal=t2.FQtyDecimal,t1.FPriceDecimal=t2.FPriceDecimal  FROM #DATA t1,t_ICItem t2,t_MeasureUnit t3  WHERE t1.FNumber=t2.FNumber  AND t2.FUnitGroupID=t3.FUnitGroupID  AND t3.FStandard=1
 UPDATE t1 SET t1.FCustNumber=t2.FNumber  FROM #DATA t1,t_Organization t2  WHERE t1.FCustID=t2.FItemID
 UPDATE #DATA SET FPrice=(CASE WHEN FQty<>0 then FAmount/FQty ELSE NULL END)
 UPDATE #DATA SET FTaxRate=(CASE WHEN FAmount<>0 THEN 100*FTaxAmount/FAmount ELSE NULL END)
-- UPDATE #Data SET FTaxRate=NULL WHERE FSumSort=101

 

--SELECT * FROM #DATA

--集团公司采购发票跟踪
--select * from #data
--参数检测
--IF CHARINDEX(@Type,N'日周月季年')=0
-- SET @Type=N'日'
  
--取排名数据到临时表
--print @p_dt_begin
--print @dt_begin
--print @date
SET ROWCOUNT @TopN
--最新名称
SELECT FCustID,Sales_Amount=SUM(FCountAmount) INTO #1 FROM #data WHERE FDate>=@dt_begin AND FDate<@Date GROUP BY FCustID ORDER BY Sales_Amount DESC
--select * from #1
--上期名次
SELECT FCustID,Sales_Amount=SUM(FCountAmount) INTO #2 FROM #data WHERE fDate>=@p_dt_begin AND fDate<@dt_begin GROUP BY FCustID ORDER BY Sales_Amount DESC

--显示结果
SELECT t1.FName 客户 ,a.Sales_Amount 含税金额,a.place 新名次,
 名次升降=CASE
  WHEN b.FCustID IS NULL THEN N'↑新上榜'
  WHEN a.Place=b.Place THEN N'－'
  WHEN a.Place>b.Place THEN N'↓'+RTRIM(a.Place-b.Place)+N'位'
  ELSE N'↑'+RTRIM(b.Place-a.Place)+N'位' END,b.place 上年名次,b.Sales_Amount 上年含税金额
into #data_1
FROM( SELECT FCustID,Sales_Amount,
  Place=(SELECT COUNT(Sales_Amount) FROM #1 WHERE Sales_Amount>aa.Sales_Amount)+1
 FROM #1 aa
)a LEFT JOIN(
 SELECT FCustID,Sales_Amount,
  Place=(SELECT COUNT(Sales_Amount) FROM #2 WHERE Sales_Amount>aa.Sales_Amount)+1
 FROM #2 aa )b ON a.FCustID=b.FCustID
LEFT JOIN T_Organization t1 on a.FCustID=t1.FItemID
ORDER BY a.Place

select * from #data_1

DROP TABLE #DATA 
DROP TABLE #DATA_1

end
-- RAISERROR('合同号不得为空！',18,18)
-- GOTO H_Error 
H_Error:
SET QUOTED_IDENTIFIER  OFF
SET ANSI_NULLS  ON

 

 

 

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

 

 

 

 

--select * from fz0012_2007

ALTER                   procedure WY_SP_yfkmfyyeb
(
-- 查询类型
-- SelType=1 精确查询 
-- SelType=0 模糊查询 
@SelType bit=1,

-- ***精确查询参数***
-- 参数    项目       数据类型
--  起 止 
-- 时间 √ √ 时间
-- 物料 √ √ 编码
-- 客户 √ √ 编码
-- 仓库 √ √ 编码
-- 操作员 -- 名称
-- 部门         -- 编码
-- 自定义1：
-- 自定义2：
-- 精确查询时，必须同时输入起止值
-- 所有的输入变量必须有默认值
@J_Operator  varchar(80)=null,
@J_DeptBegin  varchar(80)=null,
@J_DeptEnd  varchar(80)=null,

@J_TimerBegin datetime='2007-01-01 00:00:00.000',
@J_TimerEnd datetime='2007-12-31 00:00:00.000',

@J_ItemBegin varchar(80)=' F.FAA323',
@J_ItemEnd varchar(80)='F.FZW011',

@J_OrgBegin  varchar(80)=null,
@J_OrgEnd varchar(80)=null,

@J_StockBegin varchar(80)='K',
@J_StockEnd varchar(80)='K',

@J_udtVar1  varchar(80)=null,
@J_udtVar2  varchar(80)=null,

-- *** 模糊查询参数 ***
-- 参数   数据类型
-- 时间    时间
-- 物料    编码
-- 客户    编码
-- 仓库    编码
-- 操作员  名称
-- 部门    编码
-- 自定义1：
-- 自定义2：  
-- 所有的输入变量必须有默认值 varchar(80) 的默认值是'%'
@M_Operator  varchar(80)='%',
@M_Dept  varchar(80)='%',

@M_Timer datetime='1998-01-02 00:00:00.000',

@M_Item varchar(80)='%',

@M_Org  varchar(80)='%',

@M_Stock  varchar(80)='%',

@M_udtVar1  varchar(80)='%',
@M_udtVar2  varchar(80)='%'
)
AS
begin

SET NOCOUNT ON
--select distinct FItemClassID from t_itemdetailv v left join T_Account a on v.fdetailid=a.fdetailid  Where FItemClassID<>0  And  a.FNumber >= '2121'
declare @CurY  int
set @CurY=year(@J_TimerBegin)

Select t.* Into #AcctBal2707514 From
(Select b.fyear,b.fperiod,
  0 FBalanceTotal,a.FAccountID,a.FNumber ,
  convert(varchar(800),a.FName) FName,
  convert(varchar(800),'') FName1,
  a.FLevel,a.FDC,a.FDetail,a.FParentID,a.FRootID,
  Isnull(b.FDetailID,0) FDetailID,
  ISNULL(FBeginBalanceFor,0) FBeginBalanceFor,
  ISNULL(FBeginBalance,0) FBeginBalanceLocal,
  ISNULL(FEndBalanceFor,0) FEndBalanceFor,
  ISNULL(FEndBalance,0) FEndBalanceLocal,
  ISNULL(FDebitFor,0) FDebit,
  ISNULL(FDebit,0) FDebitLocal,
  ISNULL(FCreditFor,0) FCredit,
  ISNULL(FCredit,0) FCreditLocal,
  ISNULL(FYtdDebitFor,0) FYtdDebit,
  ISNULL(FYtdDebit,0) FYtdDebitLocal,
  ISNULL(FYtdCreditFor,0) FYtdCredit,
  ISNULL(FYtdCredit,0) FYtdCreditLocal,
  ISNULL(CASE WHEN FDC > 0 THEN FBeginBalanceFor END,0) FBeginDebit,
  ISNULL(CASE WHEN FDC > 0 THEN FBeginBalance END,0) FBeginDebitLocal,
  ISNULL(CASE WHEN FDC < 0 THEN -FBeginBalanceFor END,0) FBeginCredit,
  ISNULL(CASE WHEN FDC < 0 THEN -FBeginBalance END,0) FBeginCreditLocal,
  ISNULL(CASE WHEN FDC> 0 THEN FEndBalanceFor END,0) FEndDebit,
  ISNULL(CASE WHEN FDC> 0 THEN FEndBalance END,0) FEndDebitLocal,
  ISNULL(CASE WHEN FDC < 0 THEN -FEndBalanceFor END,0) FEndCredit,
  ISNULL(CASE WHEN FDC < 0 THEN -FEndBalance END,0) FEndCreditLocal 
  From t_Account a 
  LEFT OUTER JOIN   ( Select * From t_Balance  WHERE  FCurrencyID=1 And  (FYear=@CurY)  ) b On a.FAccountID = b.FAccountID  WHERE 1=1  AND  a.FNumber = '2121') t

--delete b from #AcctBal2707514 b where b.FBeginCredit=0 and b.FBeginDebit=0 and FBeginCreditLocal=0 and b.FBeginDebitLocal=0 and b.FDebit=0 and b.FCredit=0 and b.FDebitLocal=0 and b.FCreditLocal=0 and b.FYtdDebit=0 and b.FYtdCredit=0 and b.FYtdDebitLocal=0 and b.FYtdCreditLocal=0 and b.FEndBalanceFor=0 and b.FEndBalanceLocal=0
UPDATE #AcctBal2707514 SET  FName=Space(Flevel*2-2) + FName  where len(isnull(FNumber,'')) >0 and FDetailID=0 
Alter Table #AcctBal2707514 Add FItemNumber nvarchar(200) null
--select * from #AcctBal2707514

Select t1.FDetailID,convert(nvarchar(800), '') FName,convert(nvarchar(800), '') FName1,convert(nvarchar(800), '') FNumber Into #MultiItem2707515 From t_ItemDetail as t1 Inner join  ( Select DistInct FDetailID From #AcctBal2707514 Where FDetailID >0) as t2 On t1.FDetailID=t2.FDetailID   Where t1.FDetailID>0

-- Select distinct c.FITEMCLASSID,c.Fnumber from t_Itemclass c, t_Itemdetailv v Where c.FItemClassID=v.FItemClassID and c.FType=1 and c.FItemclassID>0 order by c.Fnumber
Update #MultiItem2707515 Set FName =Left(FName +'/'+ IsNull(t.FN,''),800),FNumber =Left(FNumber +'/'+ IsNull(t.FNum,''),800)  From (Select  FDetailID F1,  '['+FNumber+']'+FName FN,FNumber FNum From t_Itemdetail a, t_item b where b.FitemID>0 And b.FitemID=a.f8 ) t  Where FDetailID=t.F1

--Update #MultiItem2707515 Set FName1 =Left(FNumber +'/'+ IsNull(t.FNum,''),800)  From (Select  FDetailID F1,  '['+FNumber+']'+FName FN,FNumber FNum From t_Itemdetail a, t_item b where b.FitemID>0 And b.FitemID=a.f8 ) t  Where FDetailID=t.F1

 

Update #MultiItem2707515 Set FName =Left(FName ,800),FNumber =Left(FNumber +'/'+ IsNull(t.FNum,''),800)  From (Select  FDetailID F1,  '['+FNumber+']'+FName FN,FNumber FNum From t_Itemdetail a, t_item b where b.FitemID>0 And b.FitemID=a.f3041 ) t  Where FDetailID=t.F1
Update #MultiItem2707515 Set FName1 =Left(IsNull(t.FN,''),800)  From (Select  FDetailID F1,  '['+FNumber+']'+FName FN,FNumber FNum From t_Itemdetail a, t_item b where b.FitemID>0 And b.FitemID=a.f3041 ) t  Where FDetailID=t.F1

Update #MultiItem2707515 Set FName =STUFF(FName, 1, 1, ''),FNumber=STUFF(FNumber, 1, 1, '')

UPDATE #AcctBal2707514 Set  FName=Space(Flevel*2-2 +4) + t1.n1  ,FItemNumber = left(t1.FNumber,200)  From (Select FDetailID d1 ,FName  n1,FNumber From (Select * From #MultiItem2707515 ) t2  ) t1 Where FDetailID>0 AND FDetailID=t1.d1
UPDATE #AcctBal2707514 Set  FName1=t1.n2    From (Select FDetailID d1 ,FName1  n2,FNumber From (Select * From #MultiItem2707515 ) t2  ) t1 Where FDetailID>0 AND FDetailID=t1.d1

--Select * From #MultiItem2707515
--Select * From #AcctBal2707514

Select b.* ,FDetailIDOrder =( CASE b.FDetailID WHEN 0 THEN 0 ELSE 1 END)  into #AcctBal2707515 From #AcctBal2707514 b  WHERE 1=1 AND b.FLevel<= 1 and fnumber='2121'
--and  b.fdetailid=10583
-- or b.fdetailid=10898
--select * from #AcctBal2707515

--Union All  Select -1 FBalanceTotal,0 FAccountID,'ZZ' FNumber  ,'合计' FName,1 FLevel,1 FDC,1 FDetail,0 FParentID,0 FRootID,0 FDetailID, 0 FBeginBalanceFor, 0 FBeginBalanceLocal, 0 FEndBalanceFor, 0 FEndBalanceLocal, ISNULL(sum(FDebit),0) FDebit,  ISNULL(sum(FDebitLocal),0) FDebitLocal, ISNULL(sum(FCredit),0) FCredit, ISNULL(sum(FCreditLocal),0) FCreditLocal, ISNULL(sum(FYtdDebit),0) FYtdDebit, ISNULL(sum(FYtdDebitLocal),0) FYtdDebitLocal, ISNULL(sum(FYtdCredit),0) FYtdCredit, ISNULL(sum(FYtdCreditLocal),0) FYtdCreditLocal,  ISNULL(Sum(FBeginDebit),0) FBeginDebit, ISNULL(Sum(FBeginDebitLocal),0) FBeginDebitLocal, ISNULL(Sum(FBeginCredit),0) FBeginCredit, ISNULL(Sum(FBeginCreditLocal),0) FBeginCreditLocal, ISNULL(Sum(FEndDebit),0) FEndDebit, ISNULL(Sum(FEndDebitLocal),0) FEndDebitLocal, ISNULL(Sum(FEndCredit),0) FEndCredit, ISNULL(Sum(FEndCreditLocal),0) FEndCreditLocal ,null FItemNumber ,0 FDetailIDOrder  From #AcctBal2707514 b   Where  b.FDetailID =0 AND b.FLevel=1 AND b.FBalanceTotal=0  Order By b.FNumber,FDetailIDOrder,b.FItemNumber,b.FName

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[abcd]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[abcd]

Declare @S Nvarchar(4000)
Select @S=''

Declare @S1 Nvarchar(4000)
Select @S1=''

Declare @S2 Nvarchar(4000)
Select @S2=''

Declare @S3 Nvarchar(4000)
Select @S3=''

Declare @S4 Nvarchar(4000)
Select @S4=''

Declare @S5 Nvarchar(4000)
Select @S5=''

Declare @S6 Nvarchar(4000)
Select @S6=''

Declare @I Int

select @I=1
While @I<=12
Begin
    Select @S1=@S1+',
    sum(Case  fperiod When '+rtrim(@I)+'   Then -FBeginBalanceLocal Else 0 End) As ['+N'期初贷方余额'+Right(100+@I,2)+N'月]'
    Select @I=@I+1
End

select @I=1
While @I<=12
Begin
    Select @S2=@S2+',
    sum(Case  fperiod When '+rtrim(@I)+'   Then FDebitLocal        Else 0 End) As ['+N'本期借方'+Right(100+@I,2)+N'月]'
    Select @I=@I+1
End

select @I=1
While @I<=12
Begin
    Select @S3=@S3+',
    sum(Case  fperiod When '+rtrim(@I)+'   Then FCreditLocal       Else 0 End) As ['+N'本期贷方'+Right(100+@I,2)+N'月]'
    Select @I=@I+1
End

select @I=1
While @I<=12
Begin
    Select @S4=@S4+',
    sum(Case  fperiod When '+rtrim(@I)+'   Then FYtdCreditLocal    Else 0 End) As ['+N'累计贷方'+Right(100+@I,2)+N'月]'
    Select @I=@I+1
End

select @I=1
While @I<=12
Begin
    Select @S5=@S5+',
    sum(Case  fperiod When '+rtrim(@I)+'   Then -FEndBalanceLocal Else 0 End) As ['+N'期末贷方余额'+Right(100+@I,2)+N'月]'
    Select @I=@I+1
End

--select @s1
--select @s2
Select @S=N'Select FDetailID,fname,fname1 '
select @S6=N'  into abcd From #AcctBal2707515 group by FDetailID,fname,fname1 order by FDetailID,fname,fname1'
EXEC(@S+@S1+@S2+@S3+@S4+@S5+@S6)

select * from abcd order by FNAME,FNAME1
--select t3.FNumber,t3.FName ,t1.* from  abcd t1
--left join t_itemdetailV t2 on t1.FDetailID=t2.FDetailID
--left join t_supplier t3 on t3.FItemID=t2.FItemID
--order by t3.FNumber

--select * from #AcctBal2707514 where FDetailID=10583 or fdetailID=10898
--select * from t_supplier
--where t1.FMatFullNumber>=@J_ItemBegin and t1.FMatFullNumber<=@J_ItemEnd
--drop table abcd

 

--Select * from #AcctBal2707514 where fdetailid=10583 or fdetailid=10898
--Select * from #MultiItem2707515 where fdetailid=10583 or fdetailid=10898
drop table #AcctBal2707514
drop table #AcctBal2707515
drop table #MultiItem2707515

end
--select * from cpfxhz
-- RAISERROR('严重错误！',18,18)
-- GOTO H_Error 
H_Error:
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON

 

 

 

 

 

 

 

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

向a2插入在a1中没有的记录(a1.f2=a2.f2)
insert into a2
select * from a1 where not exists(select 1 from a2 where a1.f2=a2.f2)