create table wage(wage money,name1 char(4),sex char(2))
insert wage values(100.1,'zxy1','男')
select *
from wage
insert wage values(100.2,'zxz','男')
go
insert wage values(100.2,'zxl','女')
go
insert wage values(100.2,'zxw','女')
go
insert wage values(100.2,'zxx','男')
go
select count(name1)as samesexcount ---统计出同性之人数
from wage
group by sex
go
select sum(wage)as samesexcount ----统计出同性工资之和
from wage
group by sex
create procedure proc_print
@wage money out,
@count int out,@num int out,@money money out
as
set @wage=(select aw.avg_wage
from (select avg(wage) as avg_wage,sex from wage,Employees
where wage.EmployeeID=Employees.EmployeeID and sex='男'
group by sex) as aw)
set @count=(select an.sex_wage
from (select count(sex) as sex_wage,sex from wage,Employees
where wage.EmployeeID=Employees.EmployeeID and sex='男'
group by sex) as an)
select * from Employees
declare @wage money
declare @money money
declare @count int
declare @num int
execute proc_print @wage out,count out,@num out,@money out
print(convert(nvarchar(40),@wage))
print(convert(nvarchar(40),@num))
alter procedure proc_print
@wage money out,
@count int out
as
set @wage=(select aw.avg_wage
from (select avg(wage) as avg_wage,sex from wage,Employees
where wage.EmployeeID=Employees.EmployeeID and sex='男'
group by sex) as aw)
set @count=(select an.sex_wage
from (select count(sex) as sex_wage,sex from wage,Employees
where wage.EmployeeID=Employees.EmployeeID and sex='男'
group by sex) as an)
select * from Employees
go
declare @wage money
declare @count int
execute proc_print @wage out,@count out
print(convert(nvarchar(40),@wage))
print(convert(nvarchar(40),@count))