SQL(sqlserver)常用函数

--值类型转换
select Convert(decimal,1.8*60)
--时间差
select datediff(minute,DATEADD(HOUR,-24,GETDATE()),GETDATE())
--加减指定时间
select DATEADD(HOUR,-24,GETDATE())
--获取系统日期
select GETDATE()
--生成不重复Id
select NEWID()
--转换小写
select LOWER(NEWID())
--转换大写
select UPPER(NEWID())
--前2个字
select LEFT('123456789',2)
--后2个字
select RIGHT('123456789',2)
--替换指定字符
select replace('abc','ab','你好')
--最大值 Max(字段名)
select Max(999) as 最大值
--最小值 Min(字段名)
select Min(1) as 最小值
--总值 Sum(字段名)
select Sum(100) as 总值
--判断null转换成指定值
select isnull(111,100) as 如果null就显示第二个参数反之显示第一个
--查询非重复字段值
SELECT DISTINCT property FROM table
--表总条数
SELECT count(*) as 总条数 FROM table
--查找重复数据
select GUID from table
group by GUID having count(*)>(select top 1 count(GUID) as counts from table group by GUID order by counts desc)
--删除重复数据，只保留第一次插入的行。
delete table
where GUID in(
select GUID from table
group by GUID having count(*)>(select top 1 count(GUID) as counts from table group by GUID order by counts desc)
) and property1=property2--排序(asc:默认值,升序;desc:降序;)
SELECT DISTINCT property FROM table order by property desc
--生成随机数（0~100之间任一整数）
select CAST(FLOOR(RAND()*101)AS int)
--判断字段值，不同情况返回不同值
select (case 1 when 1 then '基数' when 2 then '偶数' when 0 then '零' else '99' end)
select (case when 1=1 then '基数' when 2=2 then '偶数' when 0=0 then '零' else '99' end)
--cmd.exe下SQL服务启动码：net start mssqlserver
--cmd.exe下SQL服务停止码：net stop mssqlserver