sql server查json数据，查时间差，CASE WHEN函数，分页语句，分组求和

SELECT *,JSON_VALUE(ExpandJson, '$.json中的字段') AS 字段名 from 表名   //查询数据库中字段保存的json数据

select *,DATEDIFF ( month,CONVERT(dateTime,'开始时间字段'),CONVERT(dateTime,'结束时间字段') ) as Months from 表名    
//查询日期的月数差，CONVERT函数是转换数据类型，DATEDIFF 函数是比较月数差


select *,CASE WHEN Months > 3 THEN 100 WHEN Months <=3 and Months >0 THEN 0 ELSE 200 END as HtState from  表名  
/// CASE WHEN THEN ELSE END 函数类似if判断，判断Months （字段名）大于3则等于100然后赋值给自定义字段名HtState

select * from(select ROW_NUMBER() over(order by DesignInstituteID desc)as rowid,* from base_DesignInstitute_view where Del=0)tempTable where rowid between 1 and 10 select count(DesignInstituteID) from base_DesignInstitute_view where Del=0  
///分页查询语句：select count(DesignInstituteID) from 表名  这句查出数据总条数， select * from(select ROW_NUMBER() over(order by DesignInstituteID desc)as rowid,* from base_DesignInstitute_view where Del=0)tempTable where rowid between 1 and 10 这句查出1到10条数据

select regionName, MaterielID,MaterielCode,MaterielName,Spec ,Unit,SUM(Quantity) as Quantity,SUM(Price) as Price,SUM(Amount) as Amount,Tax from order_Contract_view where regionName=9 and ContractID in(37,38,43) GROUP BY MaterielID,MaterielName,MaterielCode,regionName,Spec,Unit,Tax ///分组求和语句