这个MSSQL语句怎么写(groupby)
table1
id name statu
2 aaa 已完成
16 bbb 已完成
32 aaa 进行中
46 ggg 已完成
416 bbb 已完成
table2
id name count finishTime
想要把table1中，当同一个name的statu的全部为已完成时，把所有的name删除并插入到table2(table2插入时显示完成的个数)中，即是运行后两个表变成下面，请问SQL如何写啊。。谢谢
table1
id name statu
2 aaa 已完成
32 aaa 进行中
table2
id name count finishTime
1 bbb 2 getdate()
2 ggg 1 getdate()
[解决办法]
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
/*
idnamestatu
2aaa已完成
32aaa进行中
*/
select * from table2
/*
idnamecountfinishTime
1bbb22013-12-25 09:22:03.137
2ggg12013-12-25 09:22:03.137
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-25 09:20:22
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[table1]
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([id] int,[name] varchar(3),[statu] varchar(6))
insert [table1]
select 2,'aaa','已完成' union all
select 16,'bbb','已完成' union all
select 32,'aaa','进行中' union all
select 46,'ggg','已完成' union all
select 416,'bbb','已完成'
--------------开始查询--------------------------
SELECT *
FROM [table1] a
WHERE name IN (SELECT name FROM table1 WHERE statu!='已完成')
--INSERT INTO table2(name,statu,[count])
SELECT name,statu,COUNT(name)[count],GETDATE()
FROM TABLE1
WHERE name NOT IN (SELECT name FROM table1 WHERE statu!='已完成')
GROUP BY name,statu
----------------结果----------------------------
/*
id name statu
----------- ---- ------
2 aaa 已完成
32 aaa 进行中
name statu count
---- ------ ----------- -----------------------
bbb 已完成 2 2013-12-25 09:27:05.773
ggg 已完成 1 2013-12-25 09:27:05.773
*/
create table table1(id int ,name varchar(10),statu varchar(10))
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
select id=identity(int,1,1), name,count(*) as [count],getdate() as finishTime
into table2
from table1 a
where not exists(select 1 from table1 b where a.name=b.name and statu<>'已完成')
group by name
select * from table2
/*
idnamecountfinishTime
1bbb22013-12-25 09:27:47.530
2ggg12013-12-25 09:27:47.530
*/
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([id] int, [name] varchar(10), [statu] varchar(10))
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' ,'已完成'
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([id] int identity(1,1), [name] varchar(10), [count] int, [finishTime] datetime)
insert into [table2]([name],[count],[finishTime])
select [name],count([name]) as [count] ,getdate()
from [table1]
where [name] not in (select [name] from [table1] where [statu] <>'已完成' )
group by [name]
delete from [table1] where [name] not in (select [name] from [table1] where [statu] <>'已完成' )
select * from [table1]
select * from [table2]
CREATE TABLE [dbo].[Table_2](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NULL,
[count] [int] NULL,
[finishtime] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table_1](
[id] [int] NULL,
[name] [varchar](50) NULL,
[statu] [varchar](50) NULL
) ON [PRIMARY]
GO
DECLARE @name VARCHAR(100), @count int;
BEGIN
DECLARE c_test_main CURSOR FAST_FORWARD FOR
SELECT t1.name,count(t1._statu) FROM (select id,name,case when statu='已完成' then 1
when statu='进行中' then 0 end as _statu from table_1) t1 group by name having sum(t1._statu)=count(t1._statu);
OPEN c_test_main;
FETCH NEXT FROM c_test_main INTO @name,@count;
WHILE @@fetch_status = 0
BEGIN
insert into Table_2(name,count,finishtime) values(@name,@count,GETDATE());
delete from Table_1 where name=@name;
FETCH NEXT FROM c_test_main INTO @name,@count;
END;
CLOSE c_test_main;
DEALLOCATE c_test_main;
END;
go
