sqlserver 笔记

• 去掉空格&回车

REPLACE(REPLACE(@itemlist,CHAR(10),''),CHAR(13),'')

• 分隔

IF OBJECT_ID('tempdb.dbo.#Support') IS NOT NULL       
DROP   TABLE #Support    
SELECT TOP 10000 ID=IDENTITY(INT ,1,1)     
INTO   #Support    
FROM   SYSCOLUMNS a ,SYSCOLUMNS b    
    
IF OBJECT_ID('tempdb.dbo.#ItemList') IS NOT NULL       
DROP TABLE #ItemList    
SELECT     RowNumber=IDENTITY(INT,1,1),               
	CAST(LTRIM(RTRIM(SUBSTRING(a.Item,b.ID,CHARINDEX(',',a.Item+',',b.ID)-b.ID))) AS VARCHAR(8000)) AS Item     
INTO       #ItemList    
FROM       @Item AS a    
INNER JOIN #Support AS b WITH (NOLOCK)    
ON b.ID<=LEN(a.Item) AND CHARINDEX(',',','+a.Item,b.ID)=b.ID   

• 分组排序

ROW_NUMBER() OVER(PARTITION BY MerchantId ORDER BY Indate DESC) AS Seq

• 游标

DECLARE SupportCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC --定义游标
FOR
     SELECT StartDate,ENDDate,Item FROM @ALLITEM  --查出需要的集合放到游标中

OPEN SupportCursor  --打开游标
FETCH NEXT FROM SupportCursor INTO @StartDate,@ENDDate,@Item --读取第一行数据
WHILE (@@FETCH_STATUS=0)
BEGIN
        --对应操作
FETCH NEXT FROM SupportCursor INTO  @StartDate,@ENDDate,@Item  --读取下一行数据
END

CLOSE SupportCursor --关闭游标

DEALLOCATE SupportCursor --释放游标

• 归一行显示

SELECT  ID,COUNT(1) 
FROM    #A WITH(NOLOCK)
GROUP   BY ID
HAVING  COUNT(1)>1

SELECT  A.*,
        Item=
            (
            SELECT RTRIM(item)+','
            FROM   #A WITH(NOLOCK)
            WHERE ID=A.ID
            FOR XML PATH('')
            )
FROM #Model A WITH(NOLOCK)

• 动态sql

IF OBJECT_ID ('tempdb.dbo.##SoldQTY') IS NOT NULL   
  DROP TABLE ##SoldQTY 

	SELECT CONVERT(CHAR(7),SODate,120) AS [Date],b.ItemNumber,SUM(Quantity) AS SoldQTY
	INTO   ##SoldQTY
	FROM   Nsls.DBO.NewEgg_SOMXXXX As a WITH (NOLOCK)  
	INNER JOIN NSLS.[dbo].[Newegg_SoTXXXX] B WITH(NOLOCK)
	ON A.SONumber=B.SONumber
	INNER JOIN #Item C WITH(NOLOCK)
	ON B.ItemNumber=C.ItemNumber
	WHERE     SODate>=CONVERT(CHAR(07), DATEADD(MONTH, -13, @DataDate), 120)+'-01'
		  AND SODate<CONVERT(CHAR(07), @DataDate,120)+'-01'
		  AND Status<>'V'
	GROUP BY CONVERT(CHAR(7),SODate,120),b.ItemNumber 
    ORDER BY CONVERT(CHAR(7),SODate,120)


	DECLARE @Year INT=YEAR(@DataDate)
	DECLARE @SQL VARCHAR(MAX)
	DECLARE @Count INT=0

	WHILE (@Count<3)

	BEGIN


	SET @SQL='INSERT INTO  ##SoldQTY
			  SELECT CONVERT(CHAR(7),SODate,120) AS [Date],A.ItemNumber,SUM(Quantity) AS SoldQTY
			  FROM    NHissls.dbo.SOTXXXX'+CONVERT(CHAR(4),@Year-@Count)+
			  ' As a WITH (NOLOCK)  INNER JOIN NHissls.dbo.SOMXXXX'+CONVERT(CHAR(4),@Year-@Count)+'
	B WITH(NOLOCK)
	ON A.SONumber=B.SONumber
	INNER JOIN #Item C WITH(NOLOCK)
	ON A.ItemNumber=C.ItemNumber
	WHERE     SODate>=CONVERT(CHAR(07), DATEADD(MONTH, -13, ''#DataDate#''), 120)+''-01'' 
		  AND SODate<CONVERT(CHAR(07), ''#DataDate#'',120)+''-01''
		  AND Status<>''V''
		GROUP BY CONVERT(CHAR(7),SODate,120),A.ItemNumber 
		ORDER BY CONVERT(CHAR(7),SODate,120)
	'

    SET  @SQL=REPLACE(@SQL,'#DataDate#',@DataDate)
	EXEC (@SQL)

	SET @Count=@Count+1
	END

• 排除周六周日

DECLARE @Begindate DATETIME
DECLARE @Enddate DATETIME
SET @Begindate='2016-02-02 12:00:00'
SET @Enddate='2016-02-10 12:00:00'

DECLARE @Weeks INT
DECLARE @WorkDays INT
SET @Weeks=DATEDIFF(DAY,@Begindate,@Enddate)/7
SET @WorkDays=@Weeks*5
SET @Begindate=DATEADD(DAY,@Weeks*7,@Begindate)

WHILE(@Begindate<@Enddate)
     BEGIN           
           SET @WorkDays = CASE WHEN (DATEPART(WEEKDAY,@Begindate)-1) IN (1,2,3,4,5)
                                THEN @WorkDays+1
                                ELSE @WorkDays END
           SET @Begindate=@Begindate+1
     END
SELECT @WorkDays,@WEEKS