复制表中的三条记录然后，插入到表的尾部，请问sql怎么写？
原表t1字段：
id(decimal),KeHuName(varchar),AddDate(datetime),Product(varchar),ProductCount(int),AddCount(int)
id kehuname adddate product productcount addcount
1 a 2007/1/2 a1 1 12
2 a 2007/1/3 a1 2 15
3 a 2007/1/4 a2 1 11
4 a 2007/1/5 a2 1 17
5 a 2007/1/6 a3 1 19
6 b 2007/1/6 b3 1 13
7 b 2007/1/8 b2 1 13
8 c 2007/1/6 c2 1 10
我想要先找出上表中，关于kehuname= 'a '的那几条记录，并且a1,a2,a3如果存在，则需要找出关于a1,a2,a3的距离现在日期最近的那一条记录
也就是我要得到的表是：
id kehuname adddate product productcount addcount
2 a 2007/1/3 a1 2 15
4 a 2007/1/5 a2 1 17
5 a 2007/1/6 a3 1 19
然后将以上的三条记录插入到表的尾部，并且将kehuname都改为d
请问：这样的语句怎么写？
[解决办法]
建立环境
create table #temp
(id int,KeHuName varchar(10),AddDate datetime,Product varchar(10),ProductCount int,AddCount int
)
insert into #temp
select '1 ', 'a ', '2007/1/2 ', 'a1 ', '1 ', '12 ' union all select '2 ', 'a ', '2007/1/3 ', 'a1 ', '2 ', '15 ' union all select '3 ', 'a ', '2007/1/4 ', 'a2 ', '1 ', '11 ' union all select '4 ', 'a ', '2007/1/5 ', 'a2 ', '1 ', '17 ' union all select '5 ', 'a ', '2007/1/6 ', 'a3 ', '1 ', '19 ' union all select '6 ', 'b ', '2007/1/6 ', 'b3 ', '1 ', '13 ' union all select '7 ', 'b ', '2007/1/8 ', 'b2 ', '1 ', '13 ' union all select '8 ', 'c ', '2007/1/6 ', 'c2 ', '1 ', '10 '
select * from #temp a
where not exists(select 1 from #temp where AddDate> a.AddDate and Product=a.Product) and KeHuName= 'a '
-----------------
2 a 2007-01-03 00:00:00.000 a1 2 15
4 a 2007-01-05 00:00:00.000 a2 1 17
5 a 2007-01-06 00:00:00.000 a3 1 19
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb
(
id int,
kehuname varchar(1),
adddate datetime,
product varchar(10),
productcount int,
addcount int
)
insert into tb(id,kehuname,adddate,product,productcount,addcount) values(1, 'a ', '2007/1/2 ', 'a1 ',1,12)
insert into tb(id,kehuname,adddate,product,productcount,addcount) values(2, 'a ', '2007/1/3 ', 'a1 ',2,15)
insert into tb(id,kehuname,adddate,product,productcount,addcount) values(3, 'a ', '2007/1/4 ', 'a2 ',1,11)
insert into tb(id,kehuname,adddate,product,productcount,addcount) values(4, 'a ', '2007/1/5 ', 'a2 ',1,17)
insert into tb(id,kehuname,adddate,product,productcount,addcount) values(5, 'a ', '2007/1/6 ', 'a3 ',1,19)
insert into tb(id,kehuname,adddate,product,productcount,addcount) values(6, 'b ', '2007/1/6 ', 'b3 ',1,13)
insert into tb(id,kehuname,adddate,product,productcount,addcount) values(7, 'b ', '2007/1/8 ', 'b2 ',1,13)
insert into tb(id,kehuname,adddate,product,productcount,addcount) values(8, 'c ', '2007/1/6 ', 'c2 ',1,10)
insert into tb select t.id , 'd ' , t.adddate , t.product , t.productcount , t.addcount from
(
select a.* from tb a,
(
select product , max(adddate) as adddate
from tb where kehuname= 'a '
group by product
) b
where a.product = b.product and a.adddate = b.adddate
) t
select * from tb
drop table tb
--result
id kehuname adddate product productcount addcount
-- -------- ----------------------- ---------- ------------ --------
1 a 2007-01-02 00:00:00.000 a1 1 12
2 a 2007-01-03 00:00:00.000 a1 2 15
3 a 2007-01-04 00:00:00.000 a2 1 11
4 a 2007-01-05 00:00:00.000 a2 1 17
5 a 2007-01-06 00:00:00.000 a3 1 19
6 b 2007-01-06 00:00:00.000 b3 1 13
7 b 2007-01-08 00:00:00.000 b2 1 13
8 c 2007-01-06 00:00:00.000 c2 1 10
2 d 2007-01-03 00:00:00.000 a1 2 15
4 d 2007-01-05 00:00:00.000 a2 1 17
5 d 2007-01-06 00:00:00.000 a3 1 19
（所影响的行数为 11 行）
[解决办法]
select id,4 KeHuName,AddDate,Product,ProductCount,AddCount from #temp a
where not exists(select 1 from #temp where AddDate> a.AddDate and Product=a.Product) and KeHuName= 'a '
--------------------------------
2 4 2007-01-03 00:00:00.000 a1 2 15
4 4 2007-01-05 00:00:00.000 a2 1 17
5 4 2007-01-06 00:00:00.000 a3 1 19
[解决办法]
建议把这id列变成自增id，这样插入新三条记录的时候就会自动编号好了
insert #temp(KeHuName,AddDate,Product,ProductCount,AddCount)
select 'd ' KeHuName,AddDate,Product,ProductCount,AddCount from #temp a
where not exists(select 1 from #temp where AddDate> a.AddDate and Product=a.Product) and KeHuName= 'a '
[解决办法]
把过程整个顺一遍如下
1 测试环境
create table #temp
(id int,KeHuName varchar(10),AddDate datetime,Product varchar(10),ProductCount int,AddCount int
)
insert into #temp
select '1 ', 'a ', '2007/1/2 ', 'a1 ', '1 ', '12 ' union all select '2 ', 'a ', '2007/1/3 ', 'a1 ', '2 ', '15 ' union all select '3 ', 'a ', '2007/1/4 ', 'a2 ', '1 ', '11 ' union all select '4 ', 'a ', '2007/1/5 ', 'a2 ', '1 ', '17 ' union all select '5 ', 'a ', '2007/1/6 ', 'a3 ', '1 ', '19 ' union all select '6 ', 'b ', '2007/1/6 ', 'b3 ', '1 ', '13 ' union all select '7 ', 'b ', '2007/1/8 ', 'b2 ', '1 ', '13 ' union all select '8 ', 'c ', '2007/1/6 ', 'c2 ', '1 ', '10 '
2 先删除原id列，再增加新的自增id列
alter table #temp drop column id
ALTER TABLE #temp add id int IDENTITY not null
3 插入相应的纪录
insert #temp(KeHuName,AddDate,Product,ProductCount,AddCount)
select 'd ' KeHuName,AddDate,Product,ProductCount,AddCount from #temp a
where not exists(select 1 from #temp where AddDate> a.AddDate and Product=a.Product) and KeHuName= 'a '
结果：
------------
1a2007-01-02 00:00:00.000a1112
2a2007-01-03 00:00:00.000a1215
3a2007-01-04 00:00:00.000a2111
4a2007-01-05 00:00:00.000a2117
5a2007-01-06 00:00:00.000a3119
6b2007-01-06 00:00:00.000b3113
7b2007-01-08 00:00:00.000b2113
8c2007-01-06 00:00:00.000c2110
9d2007-01-03 00:00:00.000a1215
10d2007-01-05 00:00:00.000a2117
11d2007-01-06 00:00:00.000a3119
