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 ///分组求和语句
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 数字20 设计师: CSDN官方博客
应支付0元
点击重新获取
扫码支付

支付成功即可阅读