sql查询帮助-如何正确连接?

好吧,听起来问题是,如果你加入到FromPersonID=PersonID和ToPersonId=PersonID上的Person,那么PersonData中的每条记录都会有2条记录。

解决方法是连接到PersonData的两个别名副本,这样就可以将结果附加到同一行中。比如:

Select * from PersonData
LEFT JOIN Person p1 on p1.PersonID = FromPersonID
LEFT JOIN Person p2 on p2.PersonID = ToPersonID

基本上对“收件人”和“发件人”雇员字段执行相同的操作

看起来有4种可能:
FromPerson->托珀森
FromPerson->雇员

来自员工->雇员

SELECT from.name from_name, to.name to_name
FROM Person from, PersonData pd, Person to
WHERE from.Id = pd.FromPersonId
AND to.Id = pd.ToPersonId
UNION
SELECT from.name from_name, to.name to_name
FROM Person from, PersonData pd, Employee to
WHERE from.Id = pd.FromPersonId
AND to.Id = pd.ToEmployeeId
UNION
... (you get the picture)

因为您还没有将您的模式发布为SQLDDL,所以我很难看到这些表在实践中是如何工作的。以下是我的尝试:

CREATE TABLE NaturalPersons
(
 PersonId INTEGER NOT NULL UNIQUE
);

CREATE TABLE Employees
(
 PersonId INTEGER NOT NULL UNIQUE
    REFERENCES NaturalPersons (PersonId), 
 EmployeeID CHAR(3) NOT NULL UNIQUE
    CHECK (EmployeeID LIKE '[A-Z][0-9][0-9]')
);

表名“PersonData”没有透露太多信息,但从数据元素名称来看,似乎有什么东西正在从一个人/雇员转移到另一个人/雇员:

CREATE TABLE Transfers
(
 FromPersonId INTEGER 
    REFERENCES NaturalPersons (PersonId), 
 FromEmployeeID CHAR(3)
    REFERENCES Employees (EmployeeID),
 ToPersonId INTEGER 
    REFERENCES NaturalPersons (PersonId), 
 ToEmployeeID CHAR(3)
    REFERENCES Employees (EmployeeID)
);

嗯,好吧 NULL 有能力的列意味着我们不能有一个 PRIMARY KEY

对于“from”和“to”,我们只需要一种类型的ID:

ALTER TABLE Transfers ADD
 CONSTRAINT only_one_from_ID
    CHECK (
           (FromPersonId IS NULL AND FromEmployeeID IS NOT NULL)
           OR
           (FromPersonId IS NOT NULL AND FromEmployeeID IS NULL)
          );

ALTER TABLE Transfers ADD
 CONSTRAINT only_one_to_ID
    CHECK (
           (ToPersonId IS NULL AND ToEmployeeID IS NOT NULL)
           OR
           (ToPersonId IS NOT NULL AND ToEmployeeID IS NULL)
          );

我们还需要一个“常识”业务规则来防止同一个人/员工之间的转移:

ALTER TABLE Transfers ADD
 CONSTRAINT FromPersonId_cannot_be_ToPersonId
    CHECK (FromPersonId <> ToPersonId);

ALTER TABLE Transfers ADD
 CONSTRAINT FromEmployeeId_cannot_be_ToEmployeeId
    CHECK (FromEmployeeId <> ToEmployeeId);

我们已经尽力了,但我们有几个问题:

INSERT INTO NaturalPersons (PersonId) VALUES (1), (2), (3), (4);
INSERT INTO Employees (PersonId, EmployeeID) VALUES (1, 'A11'), (2, 'B22');

-- transfer to same entity - oops!:
INSERT INTO Transfers (FromPersonId, ToEmployeeID) VALUES (1, 'A11'); 

-- Duplicate transfer - oops!:
INSERT INTO Transfers (FromEmployeeId, ToPersonID) VALUES (1, 'B1'); -- duplicate
INSERT INTO Transfers (FromPersonId, ToEmployeeID) VALUES ('A1', 2); -- duplicate

如果我正确地假设员工是个人,为什么不只使用PersonID呢?