ID int primary key identity, --自动编号,主键
PName Char(10) not null, --程序员姓名
Wage int not null --工资创建一个存储过程,对程序员的工资进行分析,月薪1500到10000不等,如果有百分之五十的人薪水不到2000元,
给所有人加薪,每次加100,再进行分析,直到有一半以上的人大于2000元为止,存储过程执行完后,最终加了多少钱?create Procedure Pro_insertWage(@Id int,@PName Char(10),@Wage int,@flag int output)
as
begin
declare @MyError int
set @MyError = 0
while count(@Wage >=2000)/Count(@Id) < 0.5
begin
update ProWage set Wage = Wage+100 where Id = @Id
end
set @MyError = @MyError + @@Error
if (@MyError = 0)
begin
set @flag = 0
end
else
begin
set @flag = 1
end
endwhile后面的条件不知道改怎么写
PName Char(10) not null, --程序员姓名
Wage int not null --工资创建一个存储过程,对程序员的工资进行分析,月薪1500到10000不等,如果有百分之五十的人薪水不到2000元,
给所有人加薪,每次加100,再进行分析,直到有一半以上的人大于2000元为止,存储过程执行完后,最终加了多少钱?create Procedure Pro_insertWage(@Id int,@PName Char(10),@Wage int,@flag int output)
as
begin
declare @MyError int
set @MyError = 0
while count(@Wage >=2000)/Count(@Id) < 0.5
begin
update ProWage set Wage = Wage+100 where Id = @Id
end
set @MyError = @MyError + @@Error
if (@MyError = 0)
begin
set @flag = 0
end
else
begin
set @flag = 1
end
endwhile后面的条件不知道改怎么写
declare @MyError int
set @MyError = 0
@has int
set @has=select @has=count(*) from Wage where Wage>200
while @has/Count(@Id) < 0.5
begin
update ProWage set Wage = Wage+100 where Id = @Id
set @has=select @has=count(*) from Wage where Wage>200
end
set @MyError = @MyError + @@Error
if (@MyError = 0)
begin
set @flag = 0
end
else
begin
set @flag = 1
end试试
这句话有错
as
begin
declare @has int
declare @MyError int
set @MyError = 0
select @has=count(*) from Wage where Wage>2000
while @has/Count(*) < 0.5
begin
update ProWage set Wage = Wage+100 where Id = @Id
set @has= select @has=count(*) from Wage where Wage>2000
end
set @MyError = @MyError + @@Error
if (@MyError = 0)
begin
set @flag = 0
end
else
begin
set @flag = 1
end
end
create table t1
(
id int not null primary key identity(1,1),
name varchar(10) not null,
wage int not null
)
insert into t1 (name,wage) values ('张三',1400),('李四',2300),('王五',1800),('马六',1900),('赵大',1200)declare @a int,
@b int,
@sumwage int
set @a=0
select @b=COUNT(*) from t1
select @sumwage=SUM(wage) from t1
while @a<1
begin
if (select COUNT(*)*1.0/@b from t1 where wage>2000)>0.5
begin
set @a=1
end
else
begin
update t1 set wage=wage+100 where wage<=2000
set @a=0
end
end
select @sumwage=SUM(wage)-@sumwage from t1
print @sumwage仅供参考
as
begin
declare @MyError int
set @MyError = 0
declare @mincount int;
declare @addsalaryper int;
declare @addsalarytotal int;
select @mincount =COUNT(*) from ProWage where @Wage >=2000
while @mincount /@id < 0.5
begin
update ProWage set Wage = Wage+100 ;
set @addsalaryper =@addsalaryper+100;
set @addsalarytotal =@addsalarytotal +@addsalaryper *@Id;
select @mincount =COUNT(*) from ProWage where @Wage >=2000
end
set @MyError = @MyError + @@Error
if (@MyError = 0)
begin
set @flag = 0
end
else
begin
set @flag = 1
end
end
create table Table1
(ID int primary key identity, --自动编号,主键
PName Char(10) not null, --程序员姓名
Wage int not null )--工资insert Table1
select 'A',1300 union all
select 'B',120 union all
select 'C',1350 union all
select 'D',1600 union all
select 'E',2000 union all
select 'F',1800 union all
select 'G',1400 union all
select 'H',1100 create Procedure Pro_insertWage
as
begin
declare @Wage as int
set @Wage=0
while (select count(1) from Table1 where Wage>=2000-@Wage)<((select count(1) from Table1)/2)
begin
set @Wage=@Wage+100
end
update Table1 set Wage=Wage+@Wage
return @Wage --返回最终加了多少钱
enddeclare @r as int
exec @r=Pro_insertWage
print @r--(8 row(s) affected)
--600select * from table1ID PName Wage
----------- ---------- -----------
81 A 1900
82 B 720
83 C 1950
84 D 2200
85 E 2600
86 F 2400
87 G 2000
88 H 1700(8 row(s) affected)
create Procedure Pro_insertWage(@Id int,@PName Char(10),@Wage int,@flag int output)
as
begin
declare @MyError int
set @MyError = 0
while (select count(*) from ProWage where Wage>@Wage)*1.0/(select count(*) from ProWage) < 0.5
begin
update ProWage set Wage = Wage+100 where Id = @Id --既然给所有人加薪,怎么还有条件where Id = @Id
end
set @MyError = @MyError + @@Error
if (@MyError = 0)
begin
set @flag = 0
end
else
begin
set @flag = 1
end
end