mysql - 如何在不使用JOIN和统计员工数量的情况下显示数据？

Question

我在回答这个问题时遇到了麻烦，我似乎无法正确计数在每个部门上，只选择最高的部门并排除“达拉斯”

这就是问题

“编写一条SQL语句，显示所有部门的名称和位置(位于达拉斯的部门除外)数量最多的雇员。

您不能在 SQL 语句中使用连接操作(例如，... FROM Department,员工 WHERE …，部门 INNER JOIN 员工 ON …)。”

DEPARTMENT_ID DEPARTMENT_NAME      LOCATION           
------------- -------------------- --------------------
           10 ACCOUNTING           NEW YORK             
           20 RESEARCH             DALLAS               
           30 SALES                CHICAGO              
           40 IT                   DALLAS               
           50 EXECUTIVE            NEW YORK             
           60 MARKETING            CHICAGO              

 6 rows selected

EMPLOYEE_ID EMPLOYEE_NAME        JOB_TITLE                                          SUPERVISOR_ID HIRE_DATE     SALARY COMMISSION DEPARTMENT_ID
----------- -------------------- -------------------------------------------------- ------------- --------- ---------- ---------- -------------
       7839 KING                 PRESIDENT                                                        20-NOV-01       5000                       50 
       7596 JOST                 VICE PRESIDENT                                              7839 04-MAY-01       4500                       50 
       7603 CLARK                VICE PRESIDENT                                              7839 12-JUN-01       4000                       50 
       7566 JONES                CHIEF ACCOUNTANT                                            7596 05-APR-01       3000                       10 
       7886 STEEL                PUBLIC ACCOUNTANT                                           7566 08-MAR-03       2500                       10 
       7610 WILSON               BUSINESS ANALYST                                            7596 03-DEC-01       3000                       20 
       7999 WOLFE                TEST ANALYST                                                7610 15-FEB-02       2500                       20 
       7944 LEE                  REPORTING ANALYST                                           7610 04-SEP-06       2400                       20 
       7900 FISHER               SALES EXECUTIVE                                             7603 06-DEC-01       3000        500            30 
       7921 JACKSON              SALES REPRESENTATIVE                                        7900 25-FEB-05       2500        400            30 
       7952 LANCASTER            SALES CONSULTANT                                            7900 06-DEC-06       2000        150            30 
       7910 SMITH                DATABASE ADMINISTRATOR                                      7596 20-DEC-01       2900                       40 
       7788 SCOTT                PROGRAMMER                                                  7910 15-JAN-03       2500                       40 
       7876 ADAMS                PROGRAMMER                                                  7910 15-JAN-03       2000                       40 
       7934 MILLER               PROGRAMMER                                                  7876 25-JAN-02       1000                       40 
       8000 BREWSTER             TBA                                                              22-AUG-13       2500                          
       8100 PHILLIPS             TBA                                                         7839 21-AUG-13       2800                          
       7400 SMITH                VICE PRESIDENT                                              7839 16-FEB-01       4300                       50 
       7700 ANDRUS               PUBLIC ACCOUNTANT                                           7566 18-FEB-02       2500                       10 
       7601 SAMPSON              PROGRAMMER                                                  7910 09-JAN-01       2500                       40 
       7588 DODSON               TEST ANALYST                                                7610 02-AUG-08       2500                       20 
       7888 SANDY                SALES CONSULTANT                                            7900 05-AUG-04       2500                       30 

 22 rows selected





SELECT  DEPARTMENT_NAME, 
            location, 
            count(*)

    FROM    DEPARTMENT
    WHERE   department_id IN (  SELECT      department_id
                                FROM        department
                                WHERE       UPPER(location) <> 'DALLAS' 
                              ) 

    group by department_NAME, location
    ORDER BY    location;



DEPARTMENT_NAME      LOCATION               COUNT(*)
-------------------- -------------------- ----------
MARKETING            CHICAGO                       1 
SALES                CHICAGO                       1 
ACCOUNTING           NEW YORK                      1 
EXECUTIVE            NEW YORK                      1 

Answer 1

如果您无法使用联接，您可以尝试使用子查询

SELECT *
  FROM (SELECT d.department_name,
               d.location,
               (SELECT COUNT(employee_id)
                  FROM employee e
                 WHERE e.department_id = d.department_id) no_employees
          FROM department d
         WHERE d.location <> 'DALLAS'        
       ) t
WHERE no_employees = (SELECT COUNT(employee_id)
                        FROM employee
                       WHERE department_id IN (SELECT DISTINCT department_id
                                                 FROM department
                                                WHERE location <> 'DALLAS')
                       GROUP BY department_id
                       ORDER BY 1 DESC
                       LIMIT 1)

结果

department_name location    no_employees
SALES           CHICAGO     4
EXECUTIVE       NEW YORK    4