时间戳

首页 > 解决方案 > 这些表格设置有意义吗？尝试执行特定的 SQL 查询

sql - 这些表格设置有意义吗？尝试执行特定的 SQL 查询

问题描述

员工表

emp_no  emp_fname emp_lname job_title   job_begin
    18316   John    Barrimore   Driver  6/1/2015
    28559   Sybill  Moser       ---         8/1/2015
    28559   Sybill  Moser       Engineer    2/1/2016
    33355   Peter   Fisher      ---          ---
    2581    Elke    Hansel      Analyst 10/15/2015
    9031    Elsa    Bertoni     Manager 4/15/2015
    9031    Elsa    Bertoni     Engineer    11/15/2014
    29346   James   James       Engineer    1/4/2015
    29346   James   James       ---         12/15/2014
    30606   Kathie  Willson     Analyst 9/25/2015
    30606   Kathie  Willson     Programmer  ---
    10102   Ann Jones           Analyst 10/1/2014
    10102   Ann Jones           Manager 1/1/2012
    25348   Jerry   Smith       Engineer    2/15/2015
    --- --- --- --- ---
    31189   Julia   White   --- ---
    35500   Ben Wells   --- ---
    --- --- --- --- ---
    5500    Paul    Fisher        QA    ---
    5500    Paul    Fisher        Programmer 11/9/2016
    5500    Paul    Fisher        QA     3/22/2016
    5500    Paul    Fisher        Manager    1/18/2013
    34427   Matthew Arrow   --- ---
    --- --- --- --- ---

部门表

emp_no  dept_no dept_name   dept_location
18316   d1      R&D         Dallas, TX
28559   d1      R&D         Dallas, TX
28559   d1      R&D         Dallas, TX
33355   d1      R&D         Dallas, TX
2581    d2      Accounting  Tampa, FL
9031    d2      Accounting  Tampa, FL
9031    d2      Accounting  Tampa, FL
29346   d2      Accounting  Tampa, FL
29346   d2      Accounting  Tampa, FL
30606   d2      Accounting  Tampa, FL
30606   d2      Accounting  Tampa, FL
10102   d3      Marketing   Miami, FL
10102   d3      Marketing   Miami, FL
25348   d3      Marketing   Miami, FL
--- ---         Shipping    Atlanta, GA
31189   d5      Sales       Tampa, FL
35500   d5      Sales       Tampa, FL
--- ---     Customer Service St. Louis, MO
5500    --- --- ---
5500    --- --- ---
5500    --- --- ---
5500    --- --- ---
34427   --- --- ---
--- --- --- ---

项目表

emp_no  proj_no proj_name proj_budget
18316   p2  Gemini        $95,000.00
28559   p1  Anchor        $120,000.00
28559   p2  Gemini        $95,000.00
33355   --- --- ---
2581    p3  Jet           $186,500.00
9031    p1  Anchor        $120,000.00
9031    p3  Jet           $186,500.00
29346   p1  Anchor        $120,000.00
29346   p2  Gemini        $95,000.00
30606   p11 Sea Star    ---
30606   p20 Hunter-II   ---
10102   p1  Anchor       $120,000.00
10102   p3  Jet          $186,500.00
25348   p2  Gemini       $95,000.00
--- --- --- ---
31189   --- --- ---
35500   --- --- ---
--- --- --- ---
5500    p11 Sea Star    ---
5500    p14 Blue Sky    $300,000.00
5500    p2  Gemini      $95,000.00
5500    p20 Hunter-II   ---
34427   --- --- ---
--- ---     Winner          $300,000.00

我正在尝试找到同一部门拥有最多员工的项目，但我不确定是否可以给出如何设置表数据。我在每个表中都包含了 emp_no 列，这样我可以更轻松地进行连接，但我仍然不确定如何去做。标题给了我，我认为这是设置数据的最佳方式；但是，我对如何解决这个问题感到困惑。

标签： sqlsql-server

解决方案

假设一名员工只属于一个部门，那么不，该表设置不是一个好的架构设计。该Department表不应包括emp_no. 每个部门实体应该只有一行，并且Employee表还应该包含一个dept_no字段。

如果假设是错误的，并且可能列出的员工有多个部门，则仍然emp_no从Department表中删除并减少到每个部门一行。但是现在，您无需添加dept_no到表中，而是创建了一个包含和字段Employee的附加EmployeeDeptartments表，用于将员工分配到部门。如果您想随时间跟踪员工位置，这也很有用，知道员工过去某个时间曾在特定部门工作是很有价值的。您可以通过将日期时间字段添加到表中来做到这一点。emp_nodept_no

同样的事情也适用于Project桌子。每个项目该表应该只有一行，并且您需要一个单独的EmployeeProjects表来查看人们在做什么。

这个很重要！例如，我看到项目p1有几行，每行的预算为 120,000,000 美元。您不希望该项目表中的某一行以某种方式以与同一项目的其他行不同的预算数字结束。

稍后，您可以通过使用语句编写查询来获得看起来像当前Project或表的列表。DepartmentJOIN

“我正在努力寻找同一部门员工最多的项目。”

选择员工只能属于一个部门的选项，看起来像这样：

SELECT TOP 1 p.proj_no, p.proj_name, e.dept_no, COUNT(e.emp_no) As NumEmployeesByDept
FROM Project p
INNER JOIN EmployeeProjects ep on ep.proj_no = p.proj_no
INNER JOIN Employee e on e.emp_no = ep.emp_no
GROUP BY p.proj_no, p.proj_name, e.dept_no
ORDER BY COUNT(e.emp_no) DESC

您还可以使用窗口函数而不是聚合来处理可能存在关联的情况。

推荐阅读