菜鸟去重复之Sql

前言

本文主要是总结平时工作学习中遇到的使用Sql Server的去除重复的心得体会。

由于平时工作使用Sql并不多,此次在写本文的测试过程中,就遇到了问题,如能有幸得到高手点播,将不胜感激。

高手可以直接看个开头,直接跳过文章内容,点到后面的遇到的问题,辛苦!

准备

本文使用的工具是SQL SERVER 2008,使用的是微软的案例Northwind,选取的数据集以Products表的前10条数据为例,如下图:

Distinct

根据之后紧跟关键字distinct后的字段去除重复,而distinct只能放在所有要查询字段的前面。distinct后的字段有一个不一样即为不同。

示例:根据SupplierID,CategoryID去除重复的内容

Select distinct a.SupplierID,a.CategoryID from (SELECT TOP 10 [ProductID]
      ,[ProductName]
      ,[SupplierID]
      ,[CategoryID]
      ,[QuantityPerUnit]
      ,[UnitPrice]
      ,[UnitsInStock]
      ,[UnitsOnOrder]
      ,[ReorderLevel]
      ,[Discontinued]
  FROM [Northwind].[dbo].[Products]) a

获得结果:

Note:使用distinct是针对其后面跟着的所有字段,而不是一个或两个字段。
这直接导致如果查询的时候需要查询的字段比较多,去除重复只是根据其中的一两个字段就无法获得想要的结果。

Group by

指定由查询 (SELECT) 表达式返回的对象要分入的组。使用group by时可以巧妙地使用聚合函数达到去除重复的目的。

Select Max(a.ProductID) as ID,a.CategoryID ,a.SupplierID from (SELECT TOP 10 [ProductID]
      ,[ProductName]
      ,[SupplierID]
      ,[CategoryID]
      ,[QuantityPerUnit]
      ,[UnitPrice]
      ,[UnitsInStock]
      ,[UnitsOnOrder]
      ,[ReorderLevel]
      ,[Discontinued]
  FROM [Northwind].[dbo].[Products]) a
  group by a.CategoryID ,a.SupplierID

 获得结果:

这次可以获得去除重复过程中ID最大(获取ID最小列可以使用Min函数)的数据行,有了ID唯一标识列就可以解决上面distinct遗留下来的问题。

内联原来的表就可以获取想要的任意字段的值了。

顺带附上Min函数的结果:

Row_Number() over()

over()里面有两个参数

Partition by value_expression

将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。 value_expression 指定对结果集进行分区所依据的列。 如果未指定 PARTITION BY,则此函数将查询结果集的所有行视为单个组。

也就是说partition by后面的字段是要去重复的字段。欲知详情请点击此处
 
Order by
ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。 它是必需的。
 
Select a.ProductID,a.SupplierID,a.CategoryID, ROW_NUMBER() over(partition by CategoryID ,SupplierID order by ProductID)as RowN from (
SELECT TOP 10 [ProductID]
      ,[ProductName]
      ,[SupplierID]
      ,[CategoryID]
      ,[QuantityPerUnit]
      ,[UnitPrice]
      ,[UnitsInStock]
      ,[UnitsOnOrder]
      ,[ReorderLevel]
      ,[Discontinued]
  FROM [Northwind].[dbo].[Products]) a 

获得结果:

Note:此处的数据稍微有点问题,最后会说到。

此次并没有达到去除重复的结果,但稍微看下就发现了多了一行RowN。

这个是根据SupplierID,CategoryID分区并根据ProductID升序获得的行号。所以去除重复也就非常容易了。

Select* from (
Select a.ProductID,a.SupplierID,a.CategoryID, ROW_NUMBER() over(partition by CategoryID ,SupplierID order by ProductID)as RowN from (
SELECT TOP 10 [ProductID]
      ,[ProductName]
      ,[SupplierID]
      ,[CategoryID]
      ,[QuantityPerUnit]
      ,[UnitPrice]
      ,[UnitsInStock]
      ,[UnitsOnOrder]
      ,[ReorderLevel]
      ,[Discontinued]
  FROM [Northwind].[dbo].[Products]) a) b where b.RowN=1

获得结果:

顺带附上b.RowN=2结果:

面试问题

取出某年某月每一天的记录的第一条

姑且认为每天第一条记录是当天ID最小的那条,以下为测试使用数据集

SELECT  [OrderID]
      ,[CustomerID]
      ,[EmployeeID]
      ,[OrderDate]  
  FROM [Northwind].[dbo].[Orders]
  where DATEPART(YEAR,OrderDate)=1997 AND DATEPART(MONTH,OrderDate)=1

方法一:Group by

  with Dataset as (SELECT  [OrderID]
      ,[CustomerID]
      ,[EmployeeID]
      ,[OrderDate]  
  FROM [Northwind].[dbo].[Orders]
  where DATEPART(YEAR,OrderDate)=1997 AND DATEPART(MONTH,OrderDate)=1)
  Select a.* from Dataset a,
  (SELECT Min([OrderID]) as ID     
      ,DATEPART(DAYOFYEAR,OrderDate) as dayofOrder  
  FROM [Northwind].[dbo].[Orders]
  where DATEPART(YEAR,OrderDate)=1997 AND DATEPART(MONTH,OrderDate)=1
  group by DATEPART(DAYOFYEAR,OrderDate)) b
  where a.OrderID=b.ID

获得结果:

方法二:Row_Number() over()

 with Dataset as (SELECT  [OrderID]
      ,[CustomerID]
      ,[EmployeeID]
      ,[OrderDate]  
  FROM [Northwind].[dbo].[Orders]
  where DATEPART(YEAR,OrderDate)=1997 AND DATEPART(MONTH,OrderDate)=1)
 select a.* from(Select *,ROW_NUMBER() over(Partition by DatePart(dayofyear,OrderDate) 
 order by OrderID) as RowN from Dataset) a where a.RowN=1

获得结果:

小结:从以上两种方法可以明显感觉到第二种方法的优势,更强的灵活性,可以获得每天的第二条甚至更多,而且Order by排序有更多选择。

总结

本次关于Sql去除重复的总结就写完了,如果大家还有其他好的方法,还请分享出来。

文中如有错误或者描述不当的地方,还请指出!谢谢!

如有兴趣,继续看下面的问题,帮忙解决了,我将不胜感激!

参考资料:http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group

怪异的问题

测试过程中发现看下图:

这里面的获取的数据明显不是我想要的数据(可以参照准备里的数据集),我测试其他字段都没有问题,只有单独获取CategoryID字段的时候有问题,求高手指点。

附:测试数据库下载

 

 

posted @ 2013-06-20 01:25  胖鸟低飞  阅读(16421)  评论(14编辑  收藏