有多少员工在"xyz"地点工作并命名他们？

Question

我想找到有多少员工在"xyz"位置工作并命名他们，我有 2 个表 emp 和 deppt（dno、dname、location），这两个查询都显示计数，但它不显示所有 emp 名称，只显示第一个匹配名称。我希望显示所有名称。

//This query gives names of employess in "xyz"location.
SELECT e.emp_name
      FROM emp_mca e 
  JOIN dept_mca d 
 WHERE e.dno = d.d_no 
   AND location = "xyz";
//This query gives number of employees working at "xyz"location.
  SELECT COUNT(*)
  FROM emp_mca e 
    JOIN dept_mca d 
  WHERE e.dno = d.d_no 
    AND location = "xyz";

我想把这两点结合起来。

Answer 1

您可以添加其他子查询以获取在指定位置工作的员工总数：

SELECT e.emp_name, t.cnt     
FROM emp_mca e 
JOIN dept_mca d ON e.dno = d.d_no 
CROSS JOIN (
   SELECT COUNT(*) AS cnt
   FROM emp_mca
   JOIN dept_mca d ON dno = d_no 
   WHERE location = "xyz") AS t
WHERE d.location = "xyz";