currently look same sex some question!!!

最新推荐文章于 2025-11-13 00:57:10 发布

原创最新推荐文章于 2025-11-13 00:57:10 发布 · 2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#insert #go #table

本文通过一系列SQL语句创建并填充了一个关于工资的数据表，并利用SQL的聚合函数进行了简单的统计分析，包括计数和求和操作。此外，还定义了一个存储过程用于计算男性员工的平均工资及数量。

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))