是否可以只使用SQL和MySQL来获得下面的“输出”?
示例数据:为了更好地解释一个示例,假设我正在尝试加载一个包含员工姓名、他们过去所占用的办公室以及他们的职务历史记录的文件,这些文件由一个标签隔开。
文件:

EmployeeName<tab>OfficeHistory<tab>JobLevelHistory
John Smith<tab>501<tab>Engineer
John Smith<tab>601<tab>Senior Engineer
John Smith<tab>701<tab>Manager
Alex Button<tab>601<tab>Senior Assistant
Alex Button<tab>454<tab>Manager

注意:单表数据库是完全规范化的(尽可能多的是单表),例如,在“John Smith”的情况下,只有一个John Smith;这意味着不存在会导致引用完整性冲突的重复项。
MyOffice数据库架构包含以下表:
Employee (nId, name)
Office (nId, number)
JobTitle (nId, titleName)
Employee2Office (nEmpID, nOfficeId)
Employee2JobTitle (nEmpId, nJobTitleID)

输出:在这种情况下。这些桌子应该看起来像:
Employee
1 John Smith
2 Alex Button

Office
1 501
2 601
3 701
4 454

JobTitle
1 Engineer
2 Senior Engineer
3 Manager
4 Senior Assistant

Employee2Office
1 1
1 2
1 3
2 2
2 4

Employee2JobTitle
1 1
1 2
1 3
2 4
2 3

下面是创建数据库和表的MySQL DDL:
create database MyOffice2;

use MyOffice2;

CREATE TABLE Employee (
      id MEDIUMINT NOT NULL AUTO_INCREMENT,
      name CHAR(50) NOT NULL,
      PRIMARY KEY (id)
    ) ENGINE=InnoDB;

CREATE TABLE Office (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  office_number INT NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE JobTitle (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  title CHAR(30) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE Employee2JobTitle (
  employee_id MEDIUMINT NOT NULL,
  job_title_id MEDIUMINT NOT NULL,
  FOREIGN KEY (employee_id) REFERENCES Employee(id),
  FOREIGN KEY (job_title_id) REFERENCES JobTitle(id),
  PRIMARY KEY (employee_id, job_title_id)
) ENGINE=InnoDB;

CREATE TABLE Employee2Office (
  employee_id MEDIUMINT NOT NULL,
  office_id MEDIUMINT NOT NULL,
  FOREIGN KEY (employee_id) REFERENCES Employee(id),
  FOREIGN KEY (office_id) REFERENCES Office(id),
  PRIMARY KEY (employee_id, office_id)
) ENGINE=InnoDB;

最佳答案

可以使用传递表和触发器。定期或从你的呼叫应用程序中删除此表,只要你完成了它。

create table TmpEmp (
EmployeeName char(50) not null,
OfficeHistory int null,
JobLevelHistory char(30) null);

在此表上创建触发器
delimiter |
CREATE TRIGGER tg_TmpEmp BEFORE INSERT ON TmpEmp
FOR EACH ROW
BEGIN
IF not exists (select * from Employee where Name = NEW.EmployeeName) THEN
    INSERT INTO Employee(name)
        select NEW.EmployeeName;
END IF;
IF not exists (select * from Office where office_number = NEW.OfficeHistory) THEN
    INSERT INTO Office(office_number)
        select NEW.OfficeHistory;
END IF;
IF not exists (select * from JobTitle where title = NEW.JobLevelHistory) THEN
    INSERT INTO JobTitle(title)
        select NEW.JobLevelHistory;
END IF;
INSERT INTO Employee2JobTitle(employee_id,job_title_id)
    select E.id, T.id
    from Employee E
    inner join JobTitle T on T.title = NEW.JobLevelHistory
    where E.Name = NEW.EmployeeName
        AND not exists (select *
            from Employee2JobTitle J
            where J.employee_id = E.id and J.job_title_id = T.id);
INSERT INTO Employee2Office(employee_id,office_id)
    select E.id, O.id
    from Employee E
    inner join Office O on O.office_number = NEW.OfficeHistory
    where E.Name = NEW.EmployeeName
        AND not exists (select *
            from Employee2Office J
            where J.employee_id = E.id and J.office_id = O.id);
END; |
delimiter ;

注意:这个触发器和表的好处是,无论您使用的是加载文件还是纯插入,它都能正常工作。触发器被触发并在需要的地方添加数据。
测试一下
insert tmpEmp(EmployeeName,OfficeHistory,JobLevelHistory)
select 'John Smith',501,'Engineer' union all
select 'John Smith',601,'Senior Engineer' union all
select 'John Smith',701,'Manager' union all
select 'Alex Button',601,'Senior Assistant' union all
select 'Alex Button',454,'Manager';

truncate table tmpEmp;

关于sql - 仅使用SQL的MySQL INSERT,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/4839460/

10-12 07:33
查看更多