根据时间条件，过滤掉重复行？
[code=SQL][/code]
--测试数据
create table Orders(id int,rate int,order_day datetime,flag int)
insert into Orders values
(7810,9,2008-02-15,0),
(7810,16,2008-03-02,1),
(7810,16,2008-03-14,1),
(7810,10,2008-05-05,0),
(7810,19,2008-04-18,1),
(7810,8,2008-06-20,0),
(7810,23,2009-04-09,1),
(7810,23,2010-08-25,1),
(7702,12,2008-04-05,0),
(7210,12,2008-05-06,0),
(7230,17,2008-08-25,1),
(7560,34,2009-01-13,1),
(9840,13,2008-02-20,0),
(9840,16,2008-04-27,1),
(4567,10,2008-04-12,0),
(4567,12,2008-03-12,0),
(4567,9,2008-10-12,0),
(5689,16,2008-12-12,1),
(5689,18,2008-05-06,1),
(5689,20,2008-04-29,1);
问题和要求:
(一)id不重复的记录全部保留;
(二)id重复的记录,比如id=7810,其他重复的id要求相同:
(A)若重复的id,所有记录中flag都为0(或都为1),那么:选取最靠近2008-05-01的一条记录(且以该日期之后
且最靠近的日期记录为准;若没有,再取该日期之前且最靠近的日期记录);
(B)若重复的id,所有记录中flag既有0也有1,那么:选取最靠近2008-05-01的一条flag=1的记录(且以该日期
之后最靠近的日期且flag=1的记录为准;若没有,再取该日期之前最靠近的日期且flag=1的记录)。
测试数据的结果为:
(7810,23,2009-04-09,1)
(7702,12,2008-04-05,0)
(7210,12,2008-05-06,0)
(7230,17,2008-08-25,1)
(7560,34,2009-01-13,1)
(9840,16,2008-04-27,1)
(4567,9,2008-10-12,0)
(5689,18,2008-05-06,1)
[解决办法]
用row_number()over(partition by id order by order_date)试试。
