放在前面向大佬们求助的问题:
1、我该怎么一次性查出所有部门 所有职位的剩余人数
2、我该怎么样才能把这一个这么长的sql语句写到mybatis里面的sql配置文件中。
这是员工表:
部门表:
职位表:
分步进行查询,一步一步缩短查找范围(我之前也尝试过一股脑一波直接把三个表的数据搞定,现实告诉我我现在还不太行):
一,查出所有员工带部门的信息
SELECT e.empId,e.empName,e.empGender,e.empEmail,e.hireDate,e.phone,e.postId,e.salary,e.birthday,
d.depId,d.depName,d.msMaxNum,d.gcsMaxNum,d.sxsMaxNum
FROM emp e
INNER JOIN dep d
ON e.depId=d.depId
ORDER BY e.empId;
二,查出部门id为1的所有员工信息
SELECT zh.empId,zh.empName,zh.empGender,zh.empEmail,zh.hireDate,zh.phone,zh.postId,zh.salary,zh.birthday,
zh.depId,zh.depName,zh.msMaxNum,zh.gcsMaxNum,zh.sxsMaxNum,p.postId,p.postName FROM
(SELECT e.empId,e.empName,e.empGender,e.empEmail,e.hireDate,e.phone,e.postId,e.salary,e.birthday,
d.depId,d.depName,d.msMaxNum,d.gcsMaxNum,d.sxsMaxNum
FROM emp e
INNER JOIN dep d
ON e.depId=d.depId
ORDER BY e.empId) AS zh INNER JOIN post p ON zh.postId=p.postId AND zh.depId=1;
三,下一步(这一步有个错误):
SELECT COUNT(empId) FROM
(SELECT zh.empId,zh.empName,zh.empGender,zh.empEmail,zh.hireDate,zh.phone,zh.postId,zh.salary,zh.birthday,
zh.depId,zh.depName,zh.msMaxNum,zh.gcsMaxNum,zh.sxsMaxNum,p.postId,p.postName FROM
(SELECT e.empId,e.empName,e.empGender,e.empEmail,e.hireDate,e.phone,e.postId,e.salary,e.birthday,
d.depId,d.depName,d.msMaxNum,d.gcsMaxNum,d.sxsMaxNum
FROM emp e
INNER JOIN dep d
ON e.depId=d.depId
ORDER BY e.empId) AS zh INNER JOIN post p ON zh.postId=p.postId AND zh.depId=1) AS zh2 WHERE zh2.postId=2;
上面那一步报的数据库错误:
Query : select count(empId) from (SELECT zh.empId,zh.empName,zh.empGender,zh.empEmail,zh.hireDate,zh.phone,zh.postId,zh.salary,zh.birthd...
Error Code : 1060
Duplicate column name 'postId'
Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000
大概意思就是什么什么字段重复什么什么的~~~改!!!!
整改1
一,查出所有员工带部门的信息(把查询出来的e.postId字段加一个别名,方便下一步联合查询时不会重复使用同一字段)
SELECT e.empId,e.empName,e.empGender,e.empEmail,e.hireDate,e.phone,e.postId AS postId1,e.salary,e.birthday,
d.depId,d.depName,d.msMaxNum,d.gcsMaxNum,d.sxsMaxNum
FROM emp e
INNER JOIN dep d
ON e.depId=d.depId
ORDER BY e.empId;
整改2
二,查出部门id为1的所有员工信息(一样的!!!!!)
SELECT zh.empId,zh.empName,zh.empGender,zh.empEmail,zh.hireDate,zh.phone,zh.postId1 AS postId2,zh.salary,zh.birthday,
zh.depId,zh.depName,zh.msMaxNum,zh.gcsMaxNum,zh.sxsMaxNum,p.postName FROM
(SELECT e.empId,e.empName,e.empGender,e.empEmail,e.hireDate,e.phone,e.postId AS postId1,e.salary,e.birthday,
d.depId,d.depName,d.msMaxNum,d.gcsMaxNum,d.sxsMaxNum
FROM emp e
INNER JOIN dep d
ON e.depId=d.depId
ORDER BY e.empId) AS zh INNER JOIN post p ON zh.postId1=p.postId AND zh.depId=1;
3.
查询部门编号为1,职位编号为1的所有员工
SELECT COUNT(empId) FROM
(SELECT zh.empId,zh.empName,zh.empGender,zh.empEmail,zh.hireDate,zh.phone,zh.postId1 AS postId2,zh.salary,zh.birthday,
zh.depId,zh.depName,zh.msMaxNum,zh.gcsMaxNum,zh.sxsMaxNum,p.postName FROM
(SELECT e.empId,e.empName,e.empGender,e.empEmail,e.hireDate,e.phone,e.postId AS postId1,e.salary,e.birthday,
d.depId,d.depName,d.msMaxNum,d.gcsMaxNum,d.sxsMaxNum
FROM emp e
INNER JOIN dep d
ON e.depId=d.depId
ORDER BY e.empId) AS zh INNER JOIN post p ON zh.postId1=p.postId AND zh.depId=1) AS zh2 WHERE zh2.postId2=2;
结果!!!!就为了这一个数字,搞了我一上午。气死了~~~
因为本人技术不够,希望有哪位大佬看到之后指点我一下下。
我还有一个问题就是需要查出所有部门的所有具体职位的人数。