附加标签SQL Server的内容

• SQL Server 中删除重复记录

  2011 十二月 13

  Comments

  最常用的 T-SQL 语句：

  DELETE FROM [dbo].[myTable] WHERE 主键 NOT IN 
      (SELECT MAX(主键) From [dbo].[myTable] GROUP BY 列1, 列2, 列3) 

  SQL Server 2005 及以上版本，用 CTE：

  WITH tmpOrderdTable 
  AS 
  ( 
    SELECT  
      GroupID = ROW_NUMBER() OVER (PARTITION BY 列1, 列2, 列3 ORDER BY 主键) 
    FROM 
      [dbo].[myTable] 
  ) 
   
  DELETE FROM tmpOrderdTable WHERE GroupID > 1 

  为了提高效率可以先开启单人存取模式，删除完再恢复多人存取模式：

  # 开启单人存取模式  
  USE [master]  
  ALTER DATABASE [myDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE  
        
  # 开启多人存取模式  
  USE [master]  
  ALTER DATABASE [myDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE  

  标签： SQL, SQL Server

• SQL Server 2005 以上版本使用 CTE 分割字符串

  2011 十二月 13

  Comments

  ALTER FUNCTION [dbo].[UF_SpliteIntToTable] 
  ( 
      @String VARCHAR(8000) 
  ) 
  RETURNS @Table TABLE (Id INT) 
  AS 
  BEGIN 
      DECLARE @SpliteChar AS CHAR(1) = ','; 
           
      WITH TempTable AS 
      ( 
          SELECT CAST(SUBSTRING(@String,1,CHARINDEX(@SpliteChar,@String,1) - 1) AS INT) AS item, 
          SUBSTRING(@String,CHARINDEX(@SpliteChar,@String,1) + 1, LEN(@String)) + @SpliteChar AS list 
   
          UNION ALL 
   
          SELECT CAST(SUBSTRING(list,1,CHARINDEX(@SpliteChar,list,1) - 1) AS INT) AS item, 
          SUBSTRING(list,CHARINDEX(@SpliteChar,list,1) + 1, LEN(list)) AS list 
          FROM TempTable 
          WHERE LEN(TempTable.list) > 0 
      ) 
   
      INSERT INTO @Table SELECT item FROM TempTable WHERE item > 0 
           
      RETURN 
  END 

  标签： SQL Server, SQL