一些工作中用到过的Sql

库存表

--第二数据源
create table #data
(
  分店  varchar(10),
  款号  varchar(30),
  尺寸  varchar(30),
  色号  varchar(30),
  数量  int default(0)
)

insert into #data(分店,款号,尺寸,色号)
select '000' as 分店 , FPCode as 款号,FSize as 尺寸,FColor as 色号 from CO_StockInDetail
where  '000'='@FShopID'  and (FPCode like '@FPCode%')
--总店出库
union
select '000' as 分店, FPCode as 款号,FSize as 尺寸,FColor as 色号 from CO_StockOutDetail
left outer join CO_StockOut on CO_StockOut.FOutID=CO_StockOutDetail.FOutID 
where '000'='@FShopID'  and (FPCode like '@FPCode%')
--出库（分店入库）
union
select FShopID as 分店, FPCode as 款号,FSize as 尺寸,FColor as 色号 from CO_StockOutDetail
left outer join CO_StockOut on CO_StockOut.FOutID=CO_StockOutDetail.FOutID 
where  (FShopID='@FShopID'  Or '@FShopID'='') and (FPCode like '@FPCode%')
--总店退货
union
Select FShopID as 分店, FPCode as 款号,FSize as 尺寸, FColor as 色号 from CO_StockReturnCompany 
where  (FShopID='@FShopID'  Or '@FShopID'='') and (FPCode like '@FPCode%')
--分店退货
union
Select FShopID as 分店, FPCode as 款号,FSize as 尺寸, FColor as 色号 from CO_StockOutReturn
where (FShopID='@FShopID'  Or '@FShopID'='') and (FPCode like '@FPCode%')
--调出
union
Select FShopID as 分店, FPCode as 款号,FSize as 尺寸, FColor as 色号 from CO_StockOutChange  
where (FShopID='@FShopID'  Or '@FShopID'='') and (FPCode like '@FPCode%')
--调入
union
Select FInShopID as 分店, FPCode as 款号,FSize as 尺寸, FColor as 色号 from CO_StockOutChange  
where  (FInShopID='@FShopID'  Or '@FShopID'='') and (FInShopID like '@FPCode%')

--入库数
update #data Set 数量=
isnull((select sum(isnull(CO_StockInDetail.FQty,0)) from CO_StockInDetail
 where #data.分店='000' and #data.款号=CO_StockInDetail.FPCode and #data.尺寸=CO_StockInDetail.FSize
and #data.色号=CO_StockInDetail.FColor),0)
--总店出库数
-isnull((Select sum(isnull(CO_StockOutDetail.FQty,0)) from CO_StockOutDetail
left outer join CO_StockOut  on  CO_StockOut.FOutID=CO_StockOutDetail.FOutID
where #data.分店='000' and #data.款号=CO_StockOutDetail.FPCode and #data.尺寸=CO_StockOutDetail.FSize
and #data.色号=CO_StockOutDetail.FColor),0)
--分店入库数
+isnull((Select sum(isnull(CO_StockOutDetail.FQty,0)) from CO_StockOutDetail
left outer join CO_StockOut  on  CO_StockOut.FOutID=CO_StockOutDetail.FOutID
where #data.分店=FShopID and #data.款号=CO_StockOutDetail.FPCode and #data.尺寸=CO_StockOutDetail.FSize
and #data.色号=CO_StockOutDetail.FColor),0)
--总店退货
-isnull((Select sum(isnull(CO_StockReturnCompany.FQty,0))from CO_StockReturnCompany
where #data.分店=CO_StockReturnCompany.FShopID and #data.款号=CO_StockReturnCompany.FPCode
and #data.尺寸=CO_StockReturnCompany.FSize
and #data.色号=CO_StockReturnCompany.FColor),0)
--销售退货
-isnull((Select sum(isnull(CO_StockOutReturn.FQty,0))from CO_StockOutReturn
where #data.分店=CO_StockOutReturn.FShopID and #data.款号=CO_StockOutReturn.FPCode
and #data.尺寸=CO_StockOutReturn.FSize
and #data.色号=CO_StockOutReturn.FColor),0)
--分店调入
+isnull((Select sum(isnull(CO_StockOutChange.FQty,0))from CO_StockOutChange
where #data.分店=CO_StockOutChange.FInShopID and #data.款号=CO_StockOutChange.FPCode
and #data.尺寸=CO_StockOutChange.FSize
and #data.色号=CO_StockOutChange.FColor),0)
--分店调出
-isnull((Select sum(isnull(CO_StockOutChange.FQty,0))from CO_StockOutChange
where #data.分店=CO_StockOutChange.FShopID and #data.款号=CO_StockOutChange.FPCode
and #data.尺寸=CO_StockOutChange.FSize
and #data.色号=CO_StockOutChange.FColor),0)

select (Select FShopName from CO_Shop where CO_Shop.FShopID=#data.分店) as 分店,
款号,尺寸,
(Select A_Name From Bd_AttribInfo Where Bd_AttribInfo.A_ID=#data.色号 and  List_ID='COLOR' )
as 色号,数量 from #data
where 数量>0   and (分店='@FShopID' or '@FShopID'='')  and (款号 like '@FPCode%')
 order by    款号, 分店,色号,尺寸



游标

declare @FEmplID varchar(50)
declare @FDepaID varchar(50)
declare @FJobID varchar(50)
declare @FNewJobID varchar(50)
declare @FJobName varchar(50)
declare cur_Insert cursor for
 select FEmplID,FDepaID,FJobID from Hr_Employee
open cur_Insert
fetch next from cur_Insert into @FEmplID,@FDepaID,@FJobID
 while(@@fetch_status<>-1)
  begin 
    Select @FJobName=FJobName From Hr_Job where FJobID=@FJobID
                           if (@FNewJobID is null)
                            Begin
                      print @FEmplID
end
                         Select @FNewJobID=FJobID From HR_Job where FDepaID=@FDepaID and FJobName=@FJobName
                         Update Hr_Employee Set FJobID=@FNewJobID where FEmplID=@FEmplID
   
   fetch next from cur_Insert into @FEmplID,@FDepaID,@FJobID
  end
close cur_Insert
deallocate cur_Insert