我的SQL查询不起作用[重复]

Possible Duplicate:
Is there a way to make this SQL more efficient?

可能重复:有没有办法使这个SQL更有效?

Consider the following tables:

请考虑以下表格:

department:

deptid (type: INT)
deptname (type: TEXT)
hours (type: INT)
active (type: BIT)

department:deptid(type:INT) deptname(类型:TEXT) 小时(类型:INT) 活动(类型:BIT)

employee:

empid (type: INT)
empname (type: TEXT)
deptid (type: INT)
designation (type: TEXT)
salary (type: INT)

employee:empid(类型:INT) empname(类型:TEXT) deptid(类型:INT) 名称(类型:TEXT) 工资(类型:INT)

Write a query to return the columns empname and deptname of the employees belonging to those departments that have a head count of 4 or more. The records should be returned in alphabetical order of empname.

编写查询以返回属于那些人头数为4或更多的部门的员工的empname和deptname列。记录应按empname的字母顺序返回。

My solution is as below.

我的解决方案如下。

SELECT e.empname,d.deptname
FROM employee e,department d
WHERE e.deptid=d.deptid
      AND d.deptid
         IN
(
SELECT deptid
FROM employee
GROUP BY deptid
HAVING COUNT(*)>=4
)
ORDER BY e.empname;

1 个解决方案

#1

The answer to your question can be found on this link.

您可以在此链接上找到问题的答案。

SELECT employee.empname, department.deptname
FROM employee
INNER JOIN department ON employee.deptid = department.deptid
WHERE employee.deptid IN 
(
  SELECT employee.deptid
  FROM employee
  GROUP BY employee.deptid
  HAVING COUNT(employee.deptid) >= 4
)
ORDER BY employee.empname ASC;

Edit: If you would like me to explain anything in this solution, please don't hesitate to ask. :)

编辑:如果您希望我在此解决方案中解释任何内容,请不要犹豫。 :)

#1