SQL Server日常总结生僻小技巧 10个可能有八个平时都没用到过

--1.统计指定行的乘积:EXP(SUM(LOG(字段)))IF OBJECT_ID('tempdb.dbo.#a') IS NOT NULL DROP TABLE #aCREATE TABLE #a(i INT ,j FLOAT)INSERT #a VALUES(1,1.23)INSERT #a VALUES(1,0.59)INSERT #a VALUES(1,0.94)INSERT #a VALUES(2,1.02)INSERT #a VALUES(2,0.63)INSERT #a VALUES(3,0.98)SELECT *FROM #aSELECT i,EXP(SUM(LOG(J))) FROM #a GROUP BY i--2.没有自增列的情况下去除重复项IF OBJECT_ID('tempdb.dbo.#RepeatData') IS NOT NULL DROP TABLE #RepeatDataCREATE TABLE #RepeatData(a INT,b INT ,c INT,d INT ,e VARCHAR(100),f VARCHAR(100))INSERT #RepeatData SELECT 1,2,3,4,'中国','深圳'INSERT #RepeatData SELECT 1,2,3,4,'中国','深圳'INSERT #RepeatData SELECT 1,2,3,4,'中国','北京'INSERT #RepeatData SELECT 1,2,3,4,'中国','北京'INSERT #RepeatData SELECT 1,2,3,4,'中国','上海'INSERT #RepeatData SELECT 1,2,3,4,'中国','上海';with tt as(select *,ROW_NUMBER()over(PARTITION BY a,b,c,d,e,f ORDER by f)num from #RepeatData)delete tt where num>1SELECT *FROM #RepeatData--3.按字符串大小写模糊匹配select * from 表 where 字段 collate chinese_prc_cs_as_ws like 'A%' (查大写 )select * from 表 where 字段 collate chinese_prc_cs_as_ws like 'a%' (查小写 )--4.数据转换时，想要去掉Float到Varchar的科学计数法，用Cast转换两次即可。Float--->>Decimal--->>Varchardeclare @aa varchar(50)set @aa='6.22345e+16'select Convert(numeric,Convert(float,@aa))   select cast(cast(字段 as decimal(18,0)) as varchar)--5.大表分批删除SQL脚本WHILE 1=1BEGIN    DELETE TOP (5000) FROM TB    WHERE SYSDATE <= '2013-12-31'    IF @@ROWCOUNT < 5000        BREAK;    WAITFOR DELAY '00:00:01'END--6.根据月份返回英文对应前缀SELECT SubString('JanFebMarAprMayJunJulAugSepOctNovDec',Month(GETDATE())*3-2,3)SELECT case Month(GETDATE()) when 1 then 'Jan' when 2 then 'Feb' when 3 then 'Mar'when 4 then 'Apr' when 5 then 'May' when 6 then 'Jun' when 7 then 'Jul' when 8 then 'Aug'when 9 then 'Sep' when 10 then 'Oct' when 11 then 'Nov' when 12 then 'Dec' else 'No' END--7.存储过程执行结果字段不固定情况下插入数据到临时表IF OBJECT_ID('tempdb.dbo.#UR_ResultData') IS NOT NULL DROP TABLE #UR_ResultDataSELECT * INTO #UR_ResultData FROM OPENROWSET('SQLNCLI', '链接服务器地址';'用户名';'密码', 'SET NOCOUNT ON;SET FMTONLY OFF;EXEC 存储过程名@整型参数1 = 整型值,        @字符串参数2 =''字符串值''')