这样的sql语句如何转换成linq语法

[yaojunyi3721]

一。对于EMPLOYEES中工资高于本部门平均水平，人数多与1人的，列出部门号，人数，按部门号排序select count(*) as 员工数,a.DEPARTMENT_ID as 部门号,avg(SALARY) as 平均工资 from EMPLOYEES as a
where (select count(c.EMPLOYEE_ID) from EMPLOYEES as c where c.DEPARTMENT_ID = a.DEPARTMENT_ID and c.SALARY>(select avg(SALARY) from EMPLOYEES as b where c.DEPARTMENT_ID = b.DEPARTMENT_ID))>1
group by a.DEPARTMENT_ID order by a.DEPARTMENT_ID
二。对于EMPLOYEES中低于自己工资至少5人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数
select a.DEPARTMENT_ID,a.EMPLOYEE_NAME,a.SALARY,(select count(*) from EMPLOYEES as b
where b.SALARY < a.SALARY) as 人数 from EMPLOYEES as a
where (select count(*) from EMPLOYEES as b where b.SALARY<a.SALARY)>=5

要求尽量使用查询语法即非拉姆达表达式的方式 尽量减少分段数即能一个长查询查完的不分多段用多个var类型变量

[yaojunyi3721]

附上建表语句 方便大家测试 create database MyCompany go use MyCompany go create table Departments ( Department_ID int identity(1,1) primary key, Department_Name nvarchar(50), ) go create table Employees ( Employee_Id int identity(1,1) primary key, Employee_Name nvarchar(50), Employee_Job nvarchar(50), Salary money, Department_Id int foreign key references Departments(Department_ID) ) go insert into Departments values('财务部') insert into Departments values('行政部') insert into Departments values('开发部') insert into Employees values('曹操','组长',20000,3) insert into Employees values('刘备','经理',30000,3) insert into Employees values('诸葛亮','CEO',10000,2) insert into Employees values('黄月英','职员',5000,1) insert into Employees values('关羽','职员',8000,3) insert into Employees values('张飞','职员',8000,3) insert into Employees values('赵云','职员',7000,3) insert into Employees values('马谡','职员',4000,3)