SQL实用小语句(SQL Server)

（1）打开/关闭自增列

sqlserver 批量插入记录，对有标识列的字段要设置 setIDENTITY_INSERT 表名 on，然后再执行插入记录操作，插入完毕后恢复为 off 设置。

set IDENTITY_INSERT 表名on

set IDENTITY_INSERT 表名 off

（2）使自增长列从1开始

dbcc checkident('表名',reseed,0)

（3）刷新视图

DECLARE MyCursor CURSOR

  FOR select Namefrom dbo.sysobjectswhereOBJECTPROPERTY(id,N'IsView')= 1 and(not namein('sysconstraints','syssegments'))

 DECLARE @name varchar(40)

 OPENMyCursor 

 FETCH NEXTFROM MyCursorINTO @name

 --WHILE(@@fetch_status <> -1)

 BEGIN

  --IF(@@fetch_status <> -2)

  --begin

   exec sys.sp_refreshview@name

  --end 

  FETCH NEXT FROM MyCursor INTO @name

 END

 CLOSE MyCursor

 DEALLOCATEMyCursor

WHEREt.number>1

（4）查询当前日期

convert(varchar(10),getdate(),120)

（5）float 转varchar

select convert(nvarchar(30),convert(decimal(11,0),[CategoryId]))fromtable

SELECT CONVERT(nvarchar(100),CAST([CategoryId]ASdecimal(11,0)))from table

SELECT [CategoryId],STR([CategoryId], 11, 0)from table

--sql trim()函数去掉两头空格

--sql语法中没有直接去除两头空格的函数，但有ltrim()去除左空格rtrim()去除右空格。

--合起来用就是sql的trim()函数，即

selectltrim(rtrim(CategoryRemark))

REPLACE(STR(A.[CategoryId], 11, 0),' ','')

（6）collate Chinese_PRC_CI_AS

当跨数据库（或数据库服务器）时，有时会因为用于Join的两个表所在数据库的字符集不一致，在join…on后面直接用“t1.Name= t2.Title”会得不到预期的结果，而应该等号之前加“collateChinese_PRC_CI_AS”，如“t1.Namecollate Chinese_PRC_CI_AS = t2.Title”。

（7）分组求和

SELECT JH,

'' AS X,

'' AS Y,

SUM(A)AS A,

SUM(B)AS B,

SUM(C)AS C

FROM table

GROUP BYJH

ORDER BY JH

（8）SQL 按周,月,季度,年查询统计数据

    --①按日

    select sum(consume),day([date]) fromconsume_record where year([date]) = '2006' group by day([date])

    --②按周

    select sum(consume),datename(week,[date])from consume_record where year([date]) = '2006' group by datename(week,[date])

    --③按月

    select sum(consume),month([date]) fromconsume_record where year([date]) = '2006' group by month([date])

    --④按季

    selectsum(consume),datename(quarter,[date]) from consume_record where year([date]) ='2006' group by datename(quarter,[date])

    --⑤按年

selectsum(consume),year([date]) from consume_record where group by year([date])

（9）截取某字段的前两位

SELECT  distinct SUBSTRING(ItemCode,1,2) 

  FROM table

  where WHID='8'

  order by SUBSTRING(ItemCode,1,2) asc

（10）行转列

select Student as '姓名',

max(case Subject when '语文' then Score else 0 end) as '语文' ,

max(case Subject when '英语' then Score else 0 end ) as '英语'

from Scores

group by Student

order by Student

（11）用特定字符分割某列(使用charindex和substring)

select

substring(ContactName,charindex(' ',ContactName) +1,len(ContactName))as A 

from Northwind.dbo.customers