在项目中遇到写数据库层要处理去除重复数据的问题,使用distinct的过程中也遇到了一些新手容易遇到的简单问题。
自己查找和参考了别人的博文,自己建表试验了一些写法,对distinct和group by的使用有了一些了解。
不拿公司的项目说事,以在线图书馆系统的还书操作作为例子:
数据表 repay_record 表示还书明细信息表
id number not null, //主键
repay_code varchar2(18) not null, //还书流水号
book_id number not null, //书的id号
customer_id number not null, //顾客id
book_repay_date date not null, //还书提交时间
book_repay_status varchar2(32) not null, //还书状态,'0','1','2'
repay_handle_date date //还书处理完毕时间
解释一下应用场景:
*顾客一次可以选择多本书进行还书操作,一次操作会产生一个还书流水号(REPAY_CODE),
*书如果没有被成功还掉是可以下次再还的。
截取部分数据:
REPAY_CODE BOOK_ID BOOK_REPAY_DATE REPAY_HANDLE_DATE book_repay_status
-------------------------------------------------------------------------------------------------------------------------------------------
I2010062400000097 10007157 2010-6-24 上午 11:45:00 2010-6-24 上午 11:50:00 0
I2010062400000097 10007151 2010-6-24 上午 11:45:01 2010-6-24 上午 11:50:00 0
I2010062400000100 10007157 2010-6-24 上午 11:51:00 2010-6-24 上午 11:56:00 0
-------------------------------------------------------------------------------------------------------------------------------------------
查询需求:
查找所有状态(book_repay_status)为0的还书流水号,要求去除重复,并且按照还书提交时间(book_repay_date)排序
第一个sql:
select distinct t.repay_code
from repay_record t
where t.book_repay_status = '0'
order by t.book_repay_date
很直观,但是执行的时候报错: ORA-01791:not a SELECTed expression
官方文档的大意是:select distinct 和order by一起使用的时候,order by中必须是常量或者select列表中出现的表达式
关于这个错误,可以参考 http://blog.youkuaiyun.com/lovingprince/archive/2009/03/06/3963625.aspx,
个人觉得这篇文章讲得很不错,
大概的意思就是,distinct会使得多条记录变成一条,存在取舍,因此排序就会存在拿哪一条记录去排序的问题,
不管是单表还是多表,只要oracle自己区分不出根据哪个来排序就会出现这个异常。
第二个sql:
select distinct t.repay_code, t.book_repay_date
from repay_record t
where t.book_repay_status = '0'
order by t.book_repay_date
按照Oracle的01791错误文档修改,sql是可以执行,但是结果不是想要的结果,这条sql检测出来的结果:
REPAY_CODE BOOK_REPAY_DATE
--------------------------------------------
I2010062400000097 2010-6-24 上午 11:45:00
I2010062400000097 2010-6-24 上午 11:45:01
I2010062400000100 2010-6-24 上午 11:51:00
并没有出现将repay_code去重复的现象,
原因很简单,因为distinct是对两个列都起作用了,只有repay_code和book_repay_date都相同的情况下,才会被当做重复数据并被处理掉。
那如果这样写:
select distinct (t.repay_code), t.book_repay_date
from repay_record t
where t.book_repay_status = '0'
order by t.book_repay_date
效果是一样的,这种写法distinct依然会对两个列都起作用。
回头看我们的需求:查找所有状态(book_repay_status)为0的还书流水号,要求去除重复,并且按照还书提交时间(book_repay_date)排序
需求中有一个问题是没有明确的,即对于相同的还书流水号,如果有重复记录,排序的时间以哪一条记录的还书提交时间(book_repay_date)为准?
假设:添加限制,以重复记录中的最新(即最晚)时间为准
第三个sql:
select t.repay_code, max(t.book_repay_date) as book_repay_date
from repay_record t
where t.book_repay_status = '0'
group by t.repay_code
order by book_repay_date
执行结果:
REPAY_CODE BOOK_REPAY_DATE
--------------------------------------------
I2010062400000097 2010-6-24 上午 11:45:01
I2010062400000100 2010-6-24 上午 11:51:00
这是符合需求的sql语句。
==================================================================================
万恶的需求方开始需求变更:
上层DAO只能接受一个String类型的反馈值,不是一个map或者一个object。
修改第三个sql为:
select t.repay_code
from repay_record t
where t.book_repay_status = '0'
group by t.repay_code
order by max(book_repay_date)
轻松搞定需求方。
但是,如果写成:
select distinct t.repay_code
from repay_record t
where t.book_repay_status = '0'
order by max(t.book_repay_date)
就会报错(ora-00937),max()聚合函数必须搭配group by使用。
==================================================================================
万恶的需求方提出新的需求:
业务需求:每一个还书流水号对应的一条或者多条记录中,还书提交时间应该是相同的;
因此要提供一个查询接口供监控程序使用,获取所有不符合上述规则的还书流水号。
select t.repay_code
from repay_record t
where t.book_repay_status = '0'
group by t.repay_code
having count(distinct t.book_handle_date)>1
==================================================================================
需求方受到启发,提出新的需求:
在数据能满足上述要求(相同流水号的记录具备相同的还书提交时间)的情况下,按照旧的需求进行查询;如果数据不满足上述要求,则输出空的查询结果
即:数据库中的数据如果为
REPAY_CODE BOOK_ID BOOK_REPAY_DATE REPAY_HANDLE_DATE book_repay_status
---------------------------------------------------------------------------------------------------------
I2010062400000097 10007157 2010-6-24 上午 11:45:00 2010-6-24 上午 11:50:00 0
I2010062400000097 10007151 2010-6-24 上午 11:45:00 2010-6-24 上午 11:50:00 0
I2010062400000100 10007157 2010-6-24 上午 11:51:00 2010-6-24 上午 11:56:00 0
---------------------------------------------------------------------------------------------------------
则输出:
REPAY_CODE BOOK_REPAY_DATE
--------------------------------------------
I2010062400000097 2010-6-24 上午 11:45:00
I2010062400000100 2010-6-24 上午 11:51:00
如果数据库中的数据为:
REPAY_CODE BOOK_ID BOOK_REPAY_DATE REPAY_HANDLE_DATE book_repay_status
---------------------------------------------------------------------------------------------------------
I2010062400000097 10007157 2010-6-24 上午 11:45:00 2010-6-24 上午 11:50:00 0
I2010062400000097 10007151 2010-6-24 上午 11:45:01 2010-6-24 上午 11:50:00 0
I2010062400000100 10007157 2010-6-24 上午 11:51:00 2010-6-24 上午 11:56:00 0
---------------------------------------------------------------------------------------------------------
则输出空的查询结果
SQL:
select t.repay_code
from repay_record t
where t.book_repay_status = '0' and
not exists
(select t1.repay_code as repay_code
from repay_record t1
where t1.book_repay_status = '0'
group by t1.repay_code
having count(distinct t1.book_repay_date)>1
)
group by t.repay_code
order by max(t.book_repay_date)
===========================================================
总结一下:
1. distinct的应用场合是去除重复的数据,distinct会作用于后面的所有数据列,不可以对指定的列使用聚合函数。
group by是对记录根据指定的列进行分组,可以各个分组中指定的列应用聚合函数。
可以说,distinct能实现的功能,group by都可以实现,且group by 更为功能强大。
2. group by 配合having子句,可以对group by的分组结果进行以组为单位的筛选;这是having子句唯一的使用场合;