mysql - 员工详细信息及其工作类型

Question

我有一个包含以下表格的数据库:

 create table EMPLOYEE
 (
   Emp_ID INT NOT NULL AUTO_INCREMENT,
   Emp_FName VARCHAR(30) NOT NULL,
   Emp_LName VARCHAR(30) NOT NULL,
   Address_ID_Resident INT REFERENCES ADDRESS(Address_ID),
   JobTime_ID CHAR(1) REFERENCES JOBTIME(JobTime_ID),
   PRIMARY KEY(Emp_ID)
 );

其他表地址如下:

 create table ADDRESS
 (
    Address_ID INT NOT NULL AUTO_INCREMENT,
    Address_St VARCHAR(50) NOT NULL,
    Address_City VARCHAR(30) NOT NULL,
    Address_State VARCHAR(3) NOT NULL,
    Address_PostCode CHAR(4) NOT NULL,
    Add_TypeID CHAR(1) REFERENCES ADDRESSTYPE(Add_TypeID),
    PRIMARY KEY(Address_ID)
 );

ADDRESSTYPE 表是用来判断地址是Residential, Office, Postal

create table ADDRESSTYPE
(
  AddType_ID CHAR(1) NOT NULL,
  Add_Type VARCHAR(15) NOT NULL,
  PRIMARY KEY(AddType_ID)
);

JobTime 表描述了员工的工作是全职还是临时工。

Create table JOBTIME
(
  JobTime_ID CHAR(1) NOT NULL,
  JobTime_Desc VARCHAR(10) NOT NULL,
  PRIMARY KEY(JobTime_ID)
);

现在，由于他们每个人的薪水都不同，所以对于全职和临时工，我们有两个单独的表。

 Create table FULLTIME
 (
   Emp_ID INT NOT NULL,
   Emp_salary_yearly DOUBLE(10,2) NOT NULL,
   JobType_ID INT REFERENCES JOBTYPE(JobType_ID),
   FullTimeJob_ID CHAR(1) NOT NULL DEFAULT 'F',
   PRIMARY KEY(Emp_ID)
  );

  ALTER TABLE FULLTIME ADD FOREIGN KEY(Emp_ID) REFERENCES EMPLOYEE(Emp_ID);

  ALTER TABLE FULLTIME ADD FOREIGN KEY(FullTimeJob_ID) REFERENCES JOBFULLTIME(FullTimeJob_ID);

 Create table CASUALTIME
 (
   Emp_ID INT NOT NULL,
   Emp_salary_hourly DOUBLE(10,2) NOT NULL,
   JobType_ID INT REFERENCES JOBTYPE(JobType_ID),
   CasualJob_ID CHAR(1) NOT NULL DEFAULT 'C',
   PRIMARY KEY(EMP_ID)
 );

 ALTER TABLE CASUALTIME ADD FOREIGN KEY(Emp_ID) REFERENCES EMPLOYEE(Emp_ID);

 ALTER TABLE CASUALTIME ADD FOREIGN KEY(CasualJob_ID) REFERENCES JOBCASUALTIME(CasualJob_ID);

现在我想要名单，所有员工的完整地址按他们的薪水排序，并指出员工是全职还是临时工。这里的名称是 Name 列中表示的 FName 和 LName 的组合，以及标记为 ADDRESS 的列中 Street、Suburb State 邮政编码(例如 123 Anzac Pde、Maroubra NSW 2038)的组合。现在我知道在 Join 语句的帮助下做到这一点。但是，如果我们不使用 JOIN 语句就可以做到这一点，那么这个问题的 SQL 查询可以是什么？

我需要根据员工的工作类型从 ADDRESS 表中获取 Address，从 FULLTIME 和 CASUAL 表中获取 Salary

Answer 1

您有表继承 - CASUALTIME 和 FULLTIME 继承 EMPLOYEE。假设一名员工必须是临时工或全职员工，并且不能同时是临时工和全职员工，您可以做的是使用联合将临时工和全职员工合并到一个派生表中(我称之为 x)，并在连接到其余表之前获取单个“薪水”字段(您将需要连接):

 SELECT e.Emp_FName, e.Emp_LName, a.Address_St, a.Address_City, 
        a.Address_State, a.Address_PostCode, x.Salary
 FROM 
 (
    SELECT ct.emp_id, ct.Emp_salary_hourly AS Salary
    FROM CASUALTIME ct

    UNION

    SELECT ft.emp_id, ft.Emp_salary_yearly AS Salary
    FROM FULLTIME ft
 ) x
 INNER JOIN  EMPLOYEE e on x.Emp_ID = e.Emp_ID
 INNER JOIN ADDRESS a on e.Address_ID_Resident = a.Address_ID
 -- Can join to JobType and AddressType same way

在单个列中列出年薪和小时费率似乎有点奇怪，因为它们具有不同的单位 - 将小时费率转换为年薪或反之亦然，这样它们具有相同的单位会更有意义单位。

还有，你shouldn't be using DOUBLE 来存储财务数据 - 而使用 DECIMAL

我还假设 JOBTIME 是一个拼写错误 - 该表肯定是 JobType 吗？