sql备忘（sqlserver）

case when的用法

select (case  when nnn=17063 then 1 when nnn=11223 then 2 else 3 end) as casewhen from table1

-----------------------------------------------------------------------------------------------------------

循环

declare @i int = 1
while @i < 10
begin
select top 1 * from table where ss=@i
set @i += 1
end

---------------------------------------------------------------------------------------------------------------------------

日期&时间操作

interval间隔，常用的是YEAR、MONTH、DAY、HOUR、MINUTE、SECOND

例子：

select DATEADD(year,1,getdate())  --当前日期加一年
select DATEADD(YEAR,-1,getdate()) --当前日期减一年      

select DATEDIFF(day,'2021-11-01','2020-11-8')

----------------------------------------------------------------------------------------------------------

ISNULL(name,0) 

含义是判断name字段是否为空，如果是空则返回0

---------------------------------------------------------------------------------------------------------

row_number() 的用法

select top 100 infoid,brandid,ROW_NUMBER() over (partition by brandid order by infoid) as sort from QuoteListNew --根据brandid 分组，分组内按照infoid排序