1 部门人数大于10人的部门ID和部门名称select memberid,membername from membertype group by memberid,membername having count(id)>10 2 部门人数大于10人的部门里的所有的员工的部门名称改为Bupdate memberType set membername = 'B' where memberid in ( select memberid from membertype group by memberid having count(id)>10 )
第一次写存储过程,见笑了,有问题希望指出。--> 测试数据: [memberType] if object_id('[memberType]') is not null drop table [memberType] create table [memberType] (ID int,name varchar(3),memberID int,memberName varchar(1)) insert into [memberType] select 1,'abc',1,'a' union all select 1,'abc',2,'c' union all select 1,'abc',2,'c' union all select 1,'abc',2,'c' union all select 1,'abc',2,'c' union all select 1,'abc',2,'c' union all select 1,'abc',2,'c' union all select 1,'abc',2,'c' union all select 1,'abc',2,'c' union all select 1,'abc',2,'c' union all select 1,'abc',2,'c' union all select 1,'abc',3,'c' union all select 1,'abc',3,'c' union all select 1,'abc',3,'c' union all select 1,'abc',3,'c' union all select 1,'abc',3,'c' union all select 1,'abc',3,'c' union all select 1,'abc',3,'c' union all select 1,'abc',3,'c' union all select 1,'abc',3,'c' union all select 1,'abc',3,'c'--存储过程 Create proc p_updatemembername As set nocount off begin declare @s int select @s=count(*) from (select memberID from [memberType] group by memberid having count(memberID)>=10) t if @s > 0 begin begin tran declare @ss varchar(500) select @ss=isnull(@ss,'')+cast(memberID as varchar(50))+',' from [memberType] group by memberid having count(memberID)>=10 set @ss=left(@ss,len(@ss)-1) declare @sql varchar(8000) set @sql='update [memberType] set membername=''B'' where memberID in(' set @sql=@sql+@ss+')' exec(@sql) if @@error=0 commit tran else rollback tran end end--执行 p_updatemembername--结果 /* (30 行受影响) */
2 部门人数大于10人的部门里的所有的员工的部门名称改为Bupdate memberType
set membername = 'B'
where memberid in
(
select memberid from membertype group by memberid having count(id)>10
)
if object_id('[memberType]') is not null drop table [memberType]
create table [memberType] (ID int,name varchar(3),memberID int,memberName varchar(1))
insert into [memberType]
select 1,'abc',1,'a' union all
select 1,'abc',2,'c' union all
select 1,'abc',2,'c' union all
select 1,'abc',2,'c' union all
select 1,'abc',2,'c' union all
select 1,'abc',2,'c' union all
select 1,'abc',2,'c' union all
select 1,'abc',2,'c' union all
select 1,'abc',2,'c' union all
select 1,'abc',2,'c' union all
select 1,'abc',2,'c' union all
select 1,'abc',3,'c' union all
select 1,'abc',3,'c' union all
select 1,'abc',3,'c' union all
select 1,'abc',3,'c' union all
select 1,'abc',3,'c' union all
select 1,'abc',3,'c' union all
select 1,'abc',3,'c' union all
select 1,'abc',3,'c' union all
select 1,'abc',3,'c' union all
select 1,'abc',3,'c'--存储过程
Create proc p_updatemembername
As
set nocount off
begin
declare @s int
select @s=count(*) from (select memberID from [memberType] group by memberid having count(memberID)>=10) t
if @s > 0
begin
begin tran
declare @ss varchar(500)
select @ss=isnull(@ss,'')+cast(memberID as varchar(50))+',' from [memberType] group by memberid having count(memberID)>=10
set @ss=left(@ss,len(@ss)-1)
declare @sql varchar(8000)
set @sql='update [memberType] set membername=''B'' where memberID in('
set @sql=@sql+@ss+')'
exec(@sql)
if @@error=0
commit tran
else
rollback tran
end
end--执行
p_updatemembername--结果
/*
(30 行受影响)
*/