有没有办法使这个SQL更有效率？ [英] Is there a way to make this SQL more efficient?

问题描述

请考虑以下表格：

部门

  deptid类型：INT）
 deptname（类型：TEXT）
小时（类型：INT）
活动（类型：BIT）
  

employee

  empid（type：INT）
 empname type：TEXT）
 deptid（type：INT）
指定（类型：TEXT）
 salary（type：INT）
  

编写一个查询以返回属于那些头数为4或更多的
部门的员工的empname和deptname列。记录应按照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;您将如何改进？
 
 
 
 $ b < 
 
 
 
  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 被使用，因为一旦计数完成，已经知道
 
 
员工存在
 
 
部门已存在
 
 
 
Consider the following tables:

department

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


employee

empid       (type:INT)
empname     (type: TEXT)
deptid      (type: INT)
designation (type: TEXT)
salary      (type: 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

This was my take:
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;
How would you improve on this?
解决方案
This is shorter and probably performs faster too
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;
Start with the grouping. You don't need COUNT from the inner query.
Then, join to both tables just to get the names.

INNER JOIN is used because once the count is complete, we already know that

the employees exist
the department exists


                        
这篇关于有没有办法使这个SQL更有效率？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！