日期:2014-05-17 浏览次数:20501 次
create table table1(id int, name varchar(10), statu varchar(20))
insert into table1
select 2 ,'aaa' ,'已完成' union all
select 16 ,'bbb' ,'已完成' union all
select 32 ,'aaa' ,'进行中' union all
select 46 ,'ggg' ,'已完成' union all
select 416 ,'bbb' ,'已完成'
go
create table table2(id int identity(1,1), name varchar(10), count int, finishTime datetime)
go
insert into table2(name,count,finishTime)
select name,COUNT(*),GETDATE()
from table1
group by name
having COUNT(case when statu = '已完成' then 1 else null end) = COUNT(*)
delete table1
where name in (select name from table1 group by name
having COUNT(case when statu = '已完成' then 1 else null end) = COUNT(*))
select * from table1
/*
id name statu
2 aaa 已完成
32 aaa 进行中
*/
select * from table2
/*
id name count finishTime
1 bbb 2 2013-12-25 09:22:03.137
2 ggg 1 2013-12-25 09:22:03.137
*/