数据结构如下:pol_num co pol_iss_dt ci
4304056362 4301447485 2007-12-4 4301447485
4304056594 4301448033 2007-12-29 4301448033
4304056800 4301448092 2007-12-10 4301448092
4304056917 4300711020 2008-1-30 4300711020
4304057337 4301448475 2008-4-3 4301448475
pol_num记录编号,co客户1,CI客户2,pol_iss_dt交易时间想通过SQL,查找出重复购买的客户记录()重复购买:1、客户有多条交易记录。客户以是CO或者CI
2、重复的交易记录离最早的的交易时间>15天。
3、重复购买客户最早的那条不需要。
4304056362 4301447485 2007-12-4 4301447485
4304056594 4301448033 2007-12-29 4301448033
4304056800 4301448092 2007-12-10 4301448092
4304056917 4300711020 2008-1-30 4300711020
4304057337 4301448475 2008-4-3 4301448475
pol_num记录编号,co客户1,CI客户2,pol_iss_dt交易时间想通过SQL,查找出重复购买的客户记录()重复购买:1、客户有多条交易记录。客户以是CO或者CI
2、重复的交易记录离最早的的交易时间>15天。
3、重复购买客户最早的那条不需要。
1 111 2008-1-1 111
2 111 2008-1-1 111
3 111 2008-1-9 111
4 111 2008-3-1 111
最后查询出 4 111 2008-3-1 111
SQL怎么写,请指教?
insert into tb values('1' , '111' , '2008-1-1' , '111')
insert into tb values('2' , '111' , '2008-1-1' , '111')
insert into tb values('3' , '111' , '2008-1-9' , '111')
insert into tb values('4' , '111' , '2008-3-1' , '111')
go
select m.* from
(select * from tb where co in (select co from tb group by co having count(1) > 1)) m,
(select co, min(pol_iss_dt) pol_iss_dt from tb group by co) n
where m.co = n.co and datediff(day , n.pol_iss_dt , m.pol_iss_dt) > 15drop table tb/*
pol_num co pol_iss_dt ci
---------- ---------- ------------------------------------------------------ ----------
4 111 2008-03-01 00:00:00.000 111(所影响的行数为 1 行)
*/
--> --> (Andy)生成測試數據2009-01-14
declare @1 table([pol_num] int,[co] int,[pol_iss_dt] Datetime,[ci] int)
Insert @1
select 1,111,'2008-1-1',111 union all
select 2,111,'2008-1-1',111 union all
select 3,111,'2008-1-9',111 union all
select 4,111,'2008-4-1',111
Select *
From @1 As a
Where Exists(Select 1 From @1 Where co=a.co And pol_iss_dt+15<=a.pol_iss_dt)
And pol_num=(Select max(pol_num) From @1 Where co=a.co )
/*
pol_num co pol_iss_dt ci
----------- ----------- ----------------------- -----------
4 111 2008-04-01 00:00:00.000 111
*/