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

--1.统计指定行的乘积:EXP(SUM(LOG(字段)))
IF OBJECT_ID('tempdb.dbo.#a') IS NOT NULL DROP TABLE #a
CREATE 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 #a

SELECT i,EXP(SUM(LOG(J))) FROM #a GROUP BY i


--2.没有自增列的情况下去除重复项
IF OBJECT_ID('tempdb.dbo.#RepeatData') IS NOT NULL DROP TABLE #RepeatData
CREATE 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>1

SELECT *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--->>Varchar
declare @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=1
BEGIN
    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_ResultData
SELECT * INTO #UR_ResultData FROM OPENROWSET('SQLNCLI', '链接服务器地址';'用户名';'密码', '
SET NOCOUNT ON;SET FMTONLY OFF;
EXEC 存储过程名
@整型参数1 = 整型值,        
@字符串参数2 =''字符串值''
')