SqlServer学习(四)【复杂语句】

删除表中多余的重复记录，重复记录是根据单个字段（Id）来判断，只留有rowid最大的记录

delete from [dbo].[WList]
where [Cphone] in 

(select [Cphone]  
from [dbo].[WList]
group by [Cphone]  
having count([Cphone]) > 1)

AND ID NOT in(
SELECT  Max(ID)
FROM [dbo].[WList]
group by Cphone 
having count([Cphone])>1)

--按时间段查询，找出最近3个月的订单
--方法一
SELECT  * FROM  dbo.[Orders] where BankN=888 and DATEDIFF(day,OrdersDt,getDate()) < 90
--方法二
select * FROM dbo.[Orders] where BankN=888 and [OrdersDt] between getDate() and DATEADD(month, -2, getDate())
--方法三
select *  FROM dbo.[Orders]
with(INDEX(IDX_Bno)) 
where BankN=888 and DATEDIFF(day,OrdersDt,getDate()) < 90 

#待更新