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