34,838
社区成员




create table #temp_a(
member_id INT,
member_name VARCHAR,
member_type varchar,
date smalldatetime,
date_insert smalldatetime
)
create table #temp_b(
member_id INT,
member_name VARCHAR,
member_type varchar,
date smalldatetime
)
--原来两张表都为空的,然后开始十一号想#temp_b中插入数据
select 1,'客户a','成长期','2012-11-11 00:00:00'
union all
select 2,'客户b','成熟期','2012-11-11 00:00:00'
union all
select 3,'客户c','开发期','2012-11-11 00:00:00'
union all
select 4,'客户d','拓展期','2012-11-11 00:00:00'
--而#tesp_a中数据是从#temp_b中取来的
--所以#temp_a中数据为
1,'客户a','成长期','2012-11-11 00:00:00','2012-11-11 00:00:00'
2,'客户b','成熟期','2012-11-11 00:00:00','2012-11-11 00:00:00'
3,'客户c','开发期','2012-11-11 00:00:00','2012-11-11 00:00:00'
4,'客户d','拓展期','2012-11-11 00:00:00','2012-11-11 00:00:00'
--#temp_b中数据是每天都truncate table 后重新插入
--十二号时,先truncate table #temp_b中数据 ,再重新插入
insert into #temp_b
select 1,'客户a','成熟期','2012-11-12 00:00:00'
union all
select 2,'客户b','开发期','2012-11-12 00:00:00'
union all
select 3,'客户c','挽留期','2012-11-12 00:00:00'
union all
select 4,'客户d','拓展期','2012-11-12 00:00:00'
--而十二号时#temp_a中数据,之前的数据保留为:
--再插入由#temp_b中新的数据,但member_type不同的时候date这列不变,member_type相同的时候把新插入的数据,date这列更新为最近的时间
--,所以十二号的数据为
1,'客户a','成长期','2012-11-11 00:00:00','2012-11-11 00:00:00'
2,'客户b','成熟期','2012-11-11 00:00:00','2012-11-11 00:00:00'
3,'客户c','开发期','2012-11-11 00:00:00','2012-11-11 00:00:00'
4,'客户d','拓展期','2012-11-11 00:00:00','2012-11-11 00:00:00'
1,'客户a','成熟期','2012-11-12 00:00:00','2012-12-12 00:00:00'
2,'客户b','开发期','2012-11-12 00:00:00','2012-12-12 00:00:00'
3,'客户c','挽留期','2012-11-12 00:00:00','2012-12-12 00:00:00'
4,'客户d','拓展期','2012-11-11 00:00:00','2012-12-12 00:00:00'
--以此类推
--十三号#temp_b中数据为:
insert into #temp_b
select 1,'客户a','挽留期','2012-13-13 00:00:00'
union all
select 2,'客户b','拓展期','2012-11-13 00:00:00'
union all
select 3,'客户c','挽留期','2012-11-13 00:00:00'
union all
select 4,'客户d','拓展期','2012-11-13 00:00:00'
--#temp_a中数据为:
1,'客户a','成长期','2012-11-11 00:00:00','2012-11-11 00:00:00'
2,'客户b','成熟期','2012-11-11 00:00:00','2012-11-11 00:00:00'
3,'客户c','开发期','2012-11-11 00:00:00','2012-11-11 00:00:00'
4,'客户d','拓展期','2012-11-11 00:00:00','2012-11-11 00:00:00'
1,'客户a','成熟期','2012-11-12 00:00:00','2012-12-12 00:00:00'
2,'客户b','开发期','2012-11-12 00:00:00','2012-12-12 00:00:00'
3,'客户c','挽留期','2012-11-12 00:00:00','2012-12-12 00:00:00'
4,'客户d','拓展期','2012-11-11 00:00:00','2012-12-12 00:00:00'
1,'客户a','挽留期','2012-11-13 00:00:00','2012-12-13 00:00:00'
2,'客户b','拓展期','2012-11-13 00:00:00','2012-12-13 00:00:00'
3,'客户c','挽留期','2012-11-12 00:00:00','2012-12-13 00:00:00'
4,'客户d','拓展期','2012-11-11 00:00:00','2012-12-13 00:00:00'
--十四号#temp_b中数据为:
insert into #temp_b
select 1,'客户a','挽留期','2012-13-14 00:00:00'
union all
select 2,'客户b','开发期', '2012-11-14 00:00:00'
union all
select 3,'客户c','挽留期','2012-11-14 00:00:00'
union all
select 4,'客户d','拓展期','2012-11-14 00:00:00'
--#temp_a中数据为:
1,'客户a','成长期','2012-11-11 00:00:00','2012-11-11 00:00:00'
2,'客户b','成熟期','2012-11-11 00:00:00','2012-11-11 00:00:00'
3,'客户c','开发期','2012-11-11 00:00:00','2012-11-11 00:00:00'
4,'客户d','拓展期','2012-11-11 00:00:00','2012-11-11 00:00:00'
1,'客户a','成熟期','2012-11-12 00:00:00','2012-12-12 00:00:00'
2,'客户b','开发期','2012-11-12 00:00:00','2012-12-12 00:00:00'
3,'客户c','挽留期','2012-11-12 00:00:00','2012-12-12 00:00:00'
4,'客户d','拓展期','2012-11-11 00:00:00','2012-12-12 00:00:00'
1,'客户a','挽留期','2012-11-13 00:00:00','2012-12-13 00:00:00'
2,'客户b','拓展期','2012-11-13 00:00:00','2012-12-13 00:00:00'
3,'客户c','挽留期','2012-11-12 00:00:00','2012-12-13 00:00:00'
4,'客户d','拓展期','2012-11-11 00:00:00','2012-12-13 00:00:00'
1,'客户a','挽留期','2012-11-13 00:00:00','2012-12-14 00:00:00'
2,'客户b','开发期','2012-11-14 00:00:00','2012-12-14 00:00:00'
3,'客户c','挽留期','2012-11-12 00:00:00','2012-12-14 00:00:00'
4,'客户d','拓展期','2012-11-11 00:00:00','2012-12-14 00:00:00'
--在真实环境中数据一天的数据将近110w之多,要求的只保留四天的数据,所以希望效率高点的算法