(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