有两个表:
Employee(EmployeeId, Name, DeptId, Salary)
Dept(DeptId, DeptName )问:请找出哪个部门的人数比部门号为20的人数多?偶写的sql语句:
Select e.DeptId, d.DeptName
From ( select DeptId, count(EmployeeId) num
From Employee
Group by DeptId) e, Dept d
Where e.DeptId=d.DeptId
And e.num> (select e.num from e where DeptId=20)
Employee(EmployeeId, Name, DeptId, Salary)
Dept(DeptId, DeptName )问:请找出哪个部门的人数比部门号为20的人数多?偶写的sql语句:
Select e.DeptId, d.DeptName
From ( select DeptId, count(EmployeeId) num
From Employee
Group by DeptId) e, Dept d
Where e.DeptId=d.DeptId
And e.num> (select e.num from e where DeptId=20)
From ( select DeptId, count(EmployeeId) num
From Employee
Group by DeptId) e, ( select DeptId, count(EmployeeId) num
From Employee
Group by DeptId where DeptId=20) f,Dept d
Where e.DeptId=d.DeptId
And e.num> f.num
FROM employee a, dept b
WHERE a.deptid = b.deptid
GROUP BY deptid
HAVING COUNT (1) > (SELECT COUNT (1)
FROM employee a, dept b
WHERE b.deptid = 20 AND a.deptid = b.deptid)
FROM employee a, dept b
WHERE a.deptid = b.deptid
GROUP BY deptid,b.deptname
HAVING COUNT (1) > (SELECT COUNT (1)
FROM employee a, dept b
WHERE b.deptid = 20 AND a.deptid = b.deptid)