请考虑以下表格：
部
deptid (type:INT) deptname (type: TEXT) hours (type:INT) active (type:BIT)
雇员
empid (type:INT) empname (type: TEXT) deptid (type: INT) designation (type: TEXT) salary (type: INT)
编写查询以返回属于这些列的员工的empname和deptname列
人头数为4或以上的部门.记录应按empname的字母顺序返回
这是我的看法：
SELECT e1.empname,d.deptname from employee AS e1
FULL JOIN department AS d on e1.deptid = d.deptid
WHERE e1.deptid IN(
SELECT deptid FROM(
SELECT e2.deptid,COUNT(e2.empid)
FROM employee AS e2
GROUP BY e2.deptid
HAVING COUNT(e2.empid) >= 4
)
)
ORDER BY empname;
你会如何改进？
解决方法
这个更短,也可能更快
SELECT e1.empname,d.deptname
from (
SELECT e2.deptid
FROM employee AS e2
GROUP BY e2.deptid
HAVING COUNT(e2.empid) >= 4
) G
inner join employee AS e1 on e1.deptid = G.deptid
INNER JOIN department AS d on d.deptid = G.deptid
ORDER BY e1.empname;
从分组开始.内部查询不需要COUNT.
然后,加入两个表只是为了获取名称.
使用INNER JOIN是因为一旦计数完成,我们就已经知道了
>员工存在>部门存在
