附加标签SQL的内容

• 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

• MySQL 获取所有分类和每个分类的前几条记录

  2011 十二月 13

  Comments

  比如有文章表 Article(Id,Category,InsertDate)

  现在要用SQL找出每种类型中时间最新的前N个数据组成的集合

  SELECT A1.*  
  FROM Article AS A1  
       INNER JOIN (SELECT A.Category,A.InsertDate 
                   FROM Article AS A  
                        LEFT JOIN Article AS B  
                          ON A.Category = B.Category  
                             AND A.InsertDate <= B.InsertDate  
                   GROUP BY A.Category,A.InsertDate 
                   HAVING COUNT(B.InsertDate) <= @N 
      ) AS B1  
      ON A1.Category = B1.Category  
         AND A1.InsertDate = B1.InsertDate 
  ORDER BY A1.Category,A1.InsertDate DESC 

  @N 就是要取多少条

  标签： SQL, MySQL

• 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