34,590
社区成员
发帖
与我相关
我的任务
分享
select 人员表.工号 from 人员表 ,工资表 where 人员表.性别='女'and 工资表.工资>=1500 and 人员表.工号=工资表.工号
if object_id('[People]') is not null drop table [People]
go
if OBJECT_ID('[Sale]')is not null drop table [Sale]
go
create table [People]([No] varchar(6),[Name] varchar(6),[Sex] varchar(2))
go
create table [Sale]([No] varchar(6),[SMonth] varchar(6),[SSale] int)
go
insert [People]
select 'Gh001','张三','男' union all
select 'Gh002','李四','女' union all
select 'Gh003','王五','男' union all
select 'Gh004','Alice','女'
go
insert [Sale]
select 'Gh001','一月份',1500 union all
select 'Gh002','一月份',1800 union all
select 'Gh002','一月份',1300 union all
select 'Gh003','一月份',2000
go
--女性工资在1500以上的人员--
with TotalSale as
(
select No,SMonth,SUM(SSale) as [totalSale] from Sale group by No,SMonth
)
select p.No,p.Name,p.Sex,t.SMonth,t.totalSale from People p left join TotalSale t on p.No = t.No where p.Sex='女' and t.totalSale>1500;
--统计女性工资在1500以上的人员的个数--
with TotalSale as
(
select No,SMonth,SUM(SSale) as [totalSale] from Sale group by No,SMonth
)
select COUNT(1) as [人数] from People p left join TotalSale t on p.No = t.No where p.Sex='女' and t.totalSale>1500;
select name,count(name) from employee ,salares where employee.sex='女'and salares.salary>=1500
select a.* from 人员表 a , 工资表 b
where b.工资 > 1500 and a.性别 = '女' and a.工号 = b.工号
select count(a.*) from 人员表 a , 工资表 b
where b.工资 > 1500 and a.性别 = '女' and a.工号 = b.工号
1、
select name from employee a left join salary b on a.emp_no=b.emp_no where a.sex_code='女'and salary>'1500'
2、
select COUNT(*) from employee a left join salary b on a.emp_no=b.emp_no where a.sex_code='女'and salary>'1500'
select name,count(name) from employee where sex='女'and salares>=1500