存储过程实战篇

1.查找ID是否包含在内

declare @idMy varchar(max) = '(1,2,4)'
declare @sql nvarchar(max) = 'select * from [dbo].[Employees] where [EmplId] in ' + @idMy

2.根据最大时间，去重后，获取排除重复记录
select  wx.OpenId, wx.[PrizeType],wx.Note,wx.payStatus,tr.orgNo,tr.OrgName,tr.ReallyName,tr.telephone,tr.Email,tr.Province,tr.City,wx.CreateTime from  [dbo].[WxUserPrizes] wx left  join
(
select cr.OpenId,cr.CuTime,cu.orgNo,cu.OrgName,cu.ReallyName,cu.telephone,cu.Email,cu.Province,cu.City,cu.CreateTime from (
select OpenId,max(Createtime) as CuTime from [dbo].[CouponUsers] group by OpenId
) cr left join [dbo].[CouponUsers] cu on cr.OpenId=cu.OpenId and cr.CuTime=cu.CreateTime
)  tr  on wx.OpenId=tr.OpenId where wx.[PrizeType]=2 order by wx.CreateTime

3,多重条件

CREATE PROCEDURE [dbo].[GetDEMO] (     @CID nvarchar(max),  @AcceptID nvarchar(max),  @ResourceID nvarchar(max),  @ProOwnerID nvarchar(max),  @StartData nvarchar(max),     @EndData nvarchar(max) )  AS BEGIN    DECLARE @wheres nvarchar(max)  DECLARE @whData nvarchar(max)

 set @wheres=' and 1=1 '  set @whData=' where 1=1 '    if @CID != ''   begin   set @CID='%'+@CID+'%'   SET @wheres += ' and CID like @CID '  end  if @AcceptID  != ''   begin   set @AcceptID ='%'+@AcceptID+'%'   SET @wheres += ' and LeaderId like @AcceptID '  end  if @ResourceID != ''   begin   set @ResourceID='%'+@ResourceID+'%'   SET @wheres += ' and OwnerID like @ResourceID'  end  if @ProOwnerID != ''   begin   set @ProOwnerID='%'+@ProOwnerID+'%'   SET @wheres += ' and ProOwnerId like @ProOwnerID'  end  if @StartData !='' and @EndData != ''  begin   set @whData+= ' and  Createtime<= @EndData and  Createtime >=@StartData '  end

 declare @sql nvarchar(max)

 set @sql = N'select*from (select pro.ProjectID,pro.ProjectName,pro.ContractMoney,pro.CID,Pro.OwnerID as ProOwnerId,iea.OwnerID as OwnerID,iea.OdEAMoney,iea.UnOdEAMoney from (select OwnerId,ProjectId,  Sum(case when StatusTypeID=6 then EAMoney else 0 end) as OdEAMoney,Sum(case when  StatusTypeID=1 or StatusTypeID=2 or StatusTypeID=4 or StatusTypeID=7 then EAMoney else 0 end) as UnOdEAMoney from  [dbo].[InternalEAs]  '+@whData+ 'group by  OwnerId,ProjectId) iea left join Project pro on iea.ProjectID =pro.ProjectID) ProEA left join (select Res.ResourceUserId  as ResUserId,Res.ResourceName,Res.ResourceType,Res.ResourcePrice,Res.IsDelete,Res.VoT,arcdp.Name,arcdp.LeaderId  from [dbo].[Resources] Res   left join  [dbo].[ARCDepartments]  arcdp on  Res.ARCDepartmentId=arcdp.ARCId) ReInfo on ProEA.OwnerID=ReInfo.ResUserId  where ResUserId is not null'+@wheres

 exec sp_executesql @sql,N'@CID nvarchar(max),@AcceptID nvarchar(max),@ResourceID nvarchar(max),@StartData datetime,@EndData datetime',@CID,@AcceptID,@ResourceID,@StartData ,@EndData

END

 GO

3.双表合并区最大值

select*from(select p.ProjectID,MAX(iv.InvoiceRaiseDate) as InvoiceTime from Project  p left join Invoice iv
on  p.ProjectID=iv.ProjectID  group by p.ProjectID) po  left join Project pj
on   po.ProjectID=pj.ProjectID